portable access database

[cathy68]

Hi Guru
I convert one access 2000 database into SQL server backend, this is no problem.
The problem is:
There is another portable database called traveller is a 'snapshot' copy of Master database in it's entirety, which loaded onto a laptop and carry on the plane, so the traveller database has to be standalone database(data and forms have to be same package). Now I convert the master database as SQL backend, I can’t do the traveller as linked table of SQL backend.
Original code as:
Set FSO = CreateObject("Scripting.FileSystemObject")
SFile = strAppPath & "\traveller2*" ' set source files variable
DFile = strTraveller ' set destination directory variable
FSO.CopyFile SFile, DFile, True ' copy the files overwriting as necessary
Above code I copy all master database to traveller, Which I don’t want the linked table, how can I do making it as standalone database, all the table structure and data are the same?
I prefer using VBA if possible, or maybe I have load SQL server into the laptop (this is last choice).
Any ideas are welcome.

Cathy

 

[vbajock]

You have a couple of options. One is to create a DTS package in SQL server, which will export the data from SQL server into Access tables. You can then call the DTS package from Access code. Or you can do it all in Access by adding a bunch of macros or code that contain a make table query for each of your sql tables. I prefer doing it that way because I can code my logic that checks for changes in the "on the road data" into the same package that refreshes the data from the server.

 

[tonyflavell]

Hi there

The other option is to install MSDE which is available on all Office Professional Installation disks. MSDE can deal with an SQL data source directly.

Just an idea!

Regards

Tony

 

[cathy68]

Hi vbajock
The second method you told me using VBA code. I know Access has a Docmd.trasferdatabase function. When I use:
Docmd.TrasferDatabase acImport, "ODBC Database",........(I don't write rest of it)
This function will import the tables from SQL server into the master database, but all the tables will be lost primary key.

When I use Docmd.TrasferDatabase acExport, "Microsoft Access".......
it will export current database to distance database, but current database is linked table, which means distance database will be linked tables too.

any ideas about this??? Please give me more detail about your second method.

Cathy

 

[vbajock]

If you want to preserve the key structure, use a local MSDE database as suggested by tonyflavell. The key to it is the "CopyObject" command, which can be run from a macro. Using this command will copy your SQL table with key structures intact to your local MSDE database. Look in the help file for instructions and examples. When you update your laptop, you can either flush out the old tables with the DeleteObject command followed by a CopyObject to write the new table, or write code to do your appends and updates.

 

[cathy68]

Hi vbajock
Now I understand I have to use Access project with MSDE as suggested by tonyflavell. the new question is:
the master database is .mdb file, how can I use VBA code to create a new Access project and copy all the queries, forms, reports to another directory.
original code from the master database(.mdb file) is:

Set FSO = CreateObject("Scripting.FileSystemObject")
SFile = strAppPath & "\traveller2*" ' set source files variable
DFile = strTraveller ' set destination directory variable
FSO.CopyFile SFile, DFile, True ' copy the files

Now I have to create a new Access project, import all the forms, reports, queries, then connect local MSDE database for the laptop. Can you please tell me how to achieve it??

Many thanks