Import into backend database

[67flyer]

Good day all,

I have a sql2000 database on one network and i need data copied an access database which is on another network. the networks can't be connected together.

This is what i have:
I created a db that pulls the info out of sql and puts it in access. (this is done on the command line, so one click for who ever is pulling the data). The db that is created is moved from one network to another via thumb drive(will only be done once a day).

The final db is broken into frontend and backend. From the frontend i would like to import the db tables from the thumb drive into the backend tables. the docmd.transferdatabase acimport will put it in the frontend tables.

Thanks for any help.

 

[namliam]

Why not simply activate a set of insert/update/delete queries, much simpler... and (probably) just as fast.

 

[67flyer]

Didn't think of that. i would just leave the thumb drive in and then run the querys and when done let the user know to remove the thumbdrive. How do i reference the db on the thumbdrive in a query? or do i just import the tables to the frontend and then do the querys to update then delete the imported tables. Thanks for the quick reply.

 

[67flyer]

okay got it i think. did some more searching and found the in command will do what i want for the query with another db. used: SELECT * FROM MYTBL IN 'C:\DATABASE.MDB';

 

[namliam]

Just use linked tables like you use to link your FE to the BE.
Create another set of linked tables to the Thumb and run the insert queries.

 

[67flyer]

namliam,
That would work if the thumb drive always received the same drive letter. Or if it was the same thumb drive all the time. was trying the following code:

Dim tmpLoc As String
Dim strSql

    If IsNull(txtCustFilePath) Then
        MsgBox ("Requried file path(s) are missing.")
        Exit Sub
    End If
   
    tmpLoc = me!txtCustFilePath
    strSql = "INSERT INTO tblExtract126ACFTView ( EI_ID, EI_MODEL, EI_SN, Exported ) SELECT tbl126acftview.EI_ID, tbl126acftview.EI_MODEL, tbl126acftview.EI_SN, tbl126acftview.Exported, * FROM tbl126acftview IN tmpLoc"
    DoCmd.RunSQL strSql

been searching and couldn't find an answer. tried different fixes from the searchs but no luck. It finds the drive letter but then adds tmploc as the file. (e:\tmploc) when it should be (me!txtcustfilepath on the form = e:\updatestans.mdb)

Thanks

 

[67flyer]

Okay got it now.
removed: tmpLoc = me!txtCustFilePath
changed: FROM tbl126acftview IN tmpLoc" to IN " & " ' " & me!txtcustfilepath.value & " ' " & ";"

thanks

 

[namliam]

That will work always... independant on drive letter.

Tho maybe you need to add some code to find said drive, and remap the linked tables....
But it will work always