Help with linking tables to two different DBs thru Code.

[Mtlca401]

I have a database that has two back-ends. One with a back-end that user inputs into, and another back-end that I test with. The database(front end) is on my network folder w/the test back end linked to it. Every time that I have a new roll-out or fix for the database, I create an .mde file and put it on the users network folder. Since the .mde file has my(test back-end) tables linked to it I have to relink the tables from the users back-end to the .mde file, so I don't get their information mixed up with mine. Both back-ends have the same tables except for when the user creates a new account table. The only way that the link table manager works right, is if you have the same tables in both the front_end.mdb and the .mde file. Now all the tables that are on the database(front end) are linked from my test back-end, and the tables that are on the .mde file are linked from the users back-end, just to make that clear.

My question is every time the user creates an account number how can I get that account number linked to my front_end.mdb database when I open it? I don't really need it on my test back-end because I don't fool with the account tables in the test version. I only need to do this because I need the same tables in the front-end that I need for the .mde file.

This question probably sounds confusing, sorry.

 

[markgrizzle]

Here's how I'd do it:
Modify the network folder's rights so only you have access to your test data. In your startup form, add code that looks for your test data file using the dir command. If found, the app knows you're using it, so it links to test data and exits. Users can't see, so your code responds by reattaching their links to the live data file. Use unc (\\servername\foldername\filename) so you dont have to worry about hard-coding drive letters into the paths.

The second part of your question, if I understood it, is you want your frontend to automatically link to new user-created tables in their backend?

Ideally I'd want users adding records not tables, but...
Off the top of my head, you could loop through all the tables in the network data file, and search for matches in your file, adding links for any new ones.

A simpler brute force method would be to link all the tables, then delete any in your file without the numeral suffix (Access adds this when linking tables with the same name) Loop through again and rename any with a numerical suffix, stripping off the number. Get the list of tablenames from mSysObjects, (Select Name From mSysObjects Where Type = 6)

Hope all that helps,
Mark

 

[markgrizzle]

ps - Here's the code to reattach each table.

Function ReAttachTable(strDatabase As String, strTable As String, strPath As String) As Boolean

'strDatabase - Path and name of db containing attached tbl
'strTable - table name of the attached table
'strPath - path name of the db containing the real table
'Returns True if successful, False otherwise

Dim dbsTmp As Database
Dim tdfTmp As TableDef
Dim strPrefix As String
Dim strNewConnect As String

On Error GoTo HandleError

If strDatabase = "" Then
Set dbsTmp = CurrentDb()
Else
Set dbsTmp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
End If

Set tdfTmp = dbsTmp.TableDefs(strTable)

strPrefix = Left$(tdfTmp.Connect, InStr(tdfTmp.Connect, "=&quot)
strNewConnect = strPrefix & strPath

tdfTmp.Connect = strNewConnect
tdfTmp.RefreshLink

ReAttachTable = True

ExitHere:
dbsTmp.Close
Exit Function

HandleError:
ReAttachTable = False
Resume ExitHere

End Function

 

[Mtlca401]

The only reason that they create tables, and this would help you understand a lot, is that they ask me to create new account codes all the time. They asked to create 3 of them since I posted this question. And I have other work I need to do, so I figured I'd write a little routine to allow them to do so without emailing me every ten minutes. These table are just small tables that hold billing info.

Is their no way to have it link to my test back-end also when it is created? right now when they create it, it creates the table in the users back-end and links it to their front end. it would be a lot easier to have it link to mine also.

If not how can I loop through the tables in my back-end and their back end and link tables from theirs that I do not have?