open access application from VBA

[bambo]

Hi,

I have an ms access project that is divided into 2 parts:
1. back end- only the database tables.
2. front end- all the forms/reports/ query. the database is connected as link tables to the back end file.

I'd like to replace the current back end file with an upadted back end file from a button in the front end.

basically what I need to do is:
1. disconnect the (current) back end tables - any ideas how?
2. replace the back end file (simple copy command)
3. reconnect to the (new) back end tables - any ideas how?

or maybe you have suggestions to do this in another way?

thanks,

[KGComputers]

Hi,

Are you saying your going to replace the old tables with new ones
using VBA command? Or just substituting data of the tables out from the
VBA command.

Have you tried using DAO or ADO in MS Access?

Greg

[Foxer]

Using ADO, I know you can cycle through the collection of tables and reset/update/refresh the connection string for each table.

You'd have to use a KILL command in code to delete your backend (easy if there is no file lock)
Use a copy (filecopy?) command to replace the backend
Then cycle through your collection of tables and refresh each link, or if you're lucky, find a single "refresh all linked tables" statement but I don't think there is one.

[bambo]

well, I think I can focus now on 2 problems:
1. lock problem- is there a way to release a locked mdb?
2. cycle through your collection of tables and refresh each link- how do I do that? do you have any sample code or link that shows how to do that?

thanks,

[nagasrikanth]

easy buddy...

hv a look @ da code..

Dim t As TableDef
For Each t In CurrentDb.TableDefs
    If t.Attributes = 1073741824 Then  'If its a access linked table 
        DoCmd.DeleteObject acTable, t.Name  
    End If   
Next

so, by this the source db was unlocked...
Now replace the existing db with File.copy method
Next...
relink the tables in the same way..(but make sure u have to connect to the db first using workspace or some thing)

all da bst...

Njoy..

Srikanth

[bambo]

Hi Nagasrikanth,

thank you! it looks good, I'll give it a try.
could you please show sample code how to reconnect the database?

thanks,

[nagasrikanth]

Here you go with the code for linking all the tables of source db

Sub linktables(srcpath As String)
    Dim w As Workspace
    Dim db As Database
    Dim t As TableDef
    Set w = DBEngine.Workspaces(0)
    Set db = w.OpenDatabase(srcpath)
    For Each t In db.TableDefs
        DoCmd.TransferDatabase acLink, "Microsoft Access", srcpath, acTable, t.Name, t.Name
    Next
    Set db = Nothing
    Set w = Nothing
End Sub

All da bst...

Regards
Srikanth

[nagasrikanth]

Hey..bambo...

thats not a sample code..just use the sub routine and pass the orguments to that...98% it should work w/o any prob..(i didnt tried that)

if u got any err..do let me knw..