Load All Tables From Backend Database
Feb 23 2012, 09:43 PM
From: Adelaide, South Australia
So I'm trying to migrate an existing database to a front end/back end model as part of having multiple user access.
Unfortunately the database creates a new table for each day it is used (very rarely as it is).
I've managed to get the front end to create the new table in the backend and link it, however the other front ends don't see this new table.
I tried the following code found from Google to automatically add all tables from a backed database, but it doesn't seem to work. I suspect it may be because it's looking for an mdb file, whereas my backend is an accdb...
Thoughts? I realise this is a horrible way to do it and it makes me cry every time I have to patch stuff, but I'm not in the position where I can rewrite the application from scratch (the previous developer even has spaces and slashes in field names!)
'Link All Tables From Back End Database
'The function LinkTables creates links to all the tables in the remote database.
'The DbPath parameter should contain the complete path and database name.
'The subroutine CallLinkTables has sample code to call the function.
Function LinkTables(DbPath As String) As Boolean
'This links to all the tables that reside in DbPath, whether or not they already reside in this database.
'This works when linking to an Access .mdb file, not to ODBC.
'This keeps the same table name on the front end as on the back end.
Dim rs As Recordset
On Error Resume Next
'get tables in back end database
Set rs = CurrentDb.OpenRecordset("SELECT Name " & _
"FROM MSysObjects IN '" & DbPath & "' " & _
"WHERE Type=1 AND Flags=0")
If Err <> 0 Then Exit Function
'link the tables
While Not rs.EOF
If DbPath <> Nz(DLookup("Database", "MSysObjects", "Name='" & rs!Name & "' And Type=6")) Then
'delete old link, assuming front and back end table have the same name
DoCmd.DeleteObject acTable, rs!Name
'make new link
DoCmd.TransferDatabase acLink, "Microsoft Access", DbPath, acTable, rs!Name, rs!Name
LinkTables = True
Dim Result As Boolean
Result = LinkTables("C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb")
|Go to Top · Lo-Fi Version||Time is now: 18th May 2013 - 01:24 PM|