NEED VBA CODE to Link TABLES with BACK-END DB

[ronakj16]

Hi ,
I am just a VBA beginner and i need help to do following task

I am Using Visual studio 2008 alog with MS Acces 2007.
I need a VBA code which can create a New Front End accdb file and then link it to Back-End accdb file. Can we do this task withour DAO as i have tried many method but DAO liabrary seems not working.

Plz help !!!

[OBP]

I have responded to your first question.
You do not normally create a front end using VBA, you can but it is very long winded compared to jsut creating it manually.
You can create the whole database and then split it or you can use the Table Link Manager to link to a second database using the External Data Menu to do the same.

[ronakj16]

Thanks for your Response.

Let me explain my requirement clear.
I have 3 PC all are connected to internet running Same VBA Application (Studio 2008). I am Using MSAccess 2007 as by Database. Now these DB files resided on Dropbox folder which shared by all these PCs. What i want is to each PC User to have their Own Front-End DB to reside Locally (and not to be shared by dropbox) and Back-End db Remain Shared on Dropbox. My Final Goal is to achieve Multi User access to DB without currupting it. i googled it on internet but every time i end up searching Code having DAO in it. I cannot do Front-End DB creation and linking manually So..........

1. Kindly Suggest Suitable Solution to Multiuser access Problem --OR--
2. Help me to make DAO Code run.

[OBP]

So are the 3 Front Ends going to be in Access or in Studio 2008?

[OBP]

I googled using Access on dropbox and the first hit was this one
https://www.dropboxforum.com/t5/Sharing-and-collaboration/Sharing-Access-database/td-p/148125
which basically says "don't do it".

I also found this from one of this Forum's Gurus
https://www.mrexcel.com/forum/microsoft-access/749765-sharing-access-database-via-online-storage-dropbox-google-drive-etc-can-done.html

[ronakj16]

YES you are right. I also checked these links and i understand that it is not a good solution but then is there any other option !!!!
It is just 3 PCs Locally needed this kind Setup and it is running fine so far.

Suggest me a better Option !

[OBP]

The second item I posted suggests a better option.

When you say "it is running fine so far", do you mean on dropbox?
You also said that when Googled you found the requirement for DAO, can you point me to those posts?

[ronakj16]

YES i meant by Dropbox. it is running fine. but now to make database more secure and functional i need to create FE and BE DB (not manually Ofcourse).

Goggled Post link i refer-
https://stackoverflow.com/questions/36899011/ms-access-link-table-with-vba
https://stackoverflow.com/questions/52271350/how-to-link-tables-with-vba-code-over-odbc

All of these are using DAO but my Project is throwing error at this Code:
dbs = CurrentDb()

[OBP]

One problem is that your code doesn't look anything like the code used in the 2 links.
You used the code that I posted and yet got the same error, which is very odd because my code does not reference DAO or a dbengine.
Did you also try the docmd transferdatabase method?

[ronakj16]

I did try Those Code from links but all have same issue.

Also i tried below code- Public Sub Test1()
DoCmd.TransferDatabase(acLink, "Microsoft Access", "C:\share\Access\Example Database.accdb", acTable, "Addresses", "Addresses_link")
End Sub
But again it is showing error-
Error 1 Name 'DoCmd' is not declared.

What my guess is that we are not including correct Library. i don't know which one it is but something is missing.

[OBP]

Where are you running the code?
In Studio or in Access?

[ronakj16]

Studio 2008

[OBP]

Therein lies your problem, the code that has been supplied by others and myself are to be run in an Access front end or database, using Access VBA Editor Settings and Declarations.

I have no idea if Studio can reproduce those settings.

[ronakj16]

Thanks to identify the cause of the issue.

Can you please guide me on how to do this in an Access front end or database ?

[OBP]

I would suggest that you create a New Database on your computer and then on the Main Menu click "External Data" and Then "Access", navigate to your database in Dropbox and try and import the Queries and Forms from that database.
If you can do that it will show that you can gain access to it from a new database.
Let me know how that goes.

[ronakj16]

OK. So i did try that today. I created FE and BE manually as per your instruction. For now it seems working but the only drawback is that for each PC/User initially i need to setup all this manually.
I Wish there is any working VBA Code that could do this for me.
Anyway for now it is the only option for me.

Thanks OBP..... :)

[OBP]

You only need to copy the new front end to their PCs and link it to the same back end if it doesn't do so automatically.