Re-link Access Table in VB

08-09-2004, 11:00 AM
I have several Access Databases that I need to be able to re-link for my clients to use in a VB program. My requirements are that the users simply browse to the database they need. Once they have selected the database it must be stored as the back end each time they open the program or until they move on to another database.

Problem 1.) I have tried having a table to store their connection string and everytime they open the program it will find the table and link to that string. Once they have changed the connection, the table will update with the new connection. This works fine if they are all using the same initial drive for their data storage...there are 4000 users and there can be as many different drive letters. I need something that no matter what drive they want to use they can find the backend.

Problem 2.) I have tried using an .udl file on their C: drives and if they re-link to another table that information would edit via VB using Notepad. That did not work because once it is edited using notepad it makes the .udl un-usable. Therefore when they try to run the program it gives them the following error:
"the File %1 is not valid compound file"

this is the code I am using to udpate the .udl file:

Private Sub cmdLinkNew_Click()
Dim myDB As ADODB.Connection
Dim myRS As ADODB.Recordset
Dim strCnn As String
Dim DLink As String
Dim DLink2 As String
Dim DLink3 As ADODB.Connection

Set gblConnection = New ADODB.Connection
If Right(LFile.Path, 1) <> "\" Then
NameOfFile = LFile.Path + "\" + LFile.FileName
NameOfFile = LFile.Path + LFile.FileName
End If
Text1 = NameOfFile
gblConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + NameOfFile + ";Persist Security Info=False"
' gblConnection = gblConnection + NameOfFile + ";Persist Security Info=False"
DLink = "[oledb]"
' DLink = DLink + "; Everything after this line is an OLE DB initstring" + Chr$(10)
DLink2 = "; Everything after this line is an OLE DB initstring"
' DLink = DLink + gblConnection
Set DLink3 = New ADODB.Connection
DLink3 = gblConnection
MsgBox DLink + Chr$(13) + DLink2 + Chr$(13) = DLink3
Open "C:\NEP.udl" For Output As #1
Print #1, DLink Print #1, DLink2
Print #1, DLink3
Close #1
End sub

* in an earlier thread someone informed me that the text file is Unicode and the .udl in not, therefore that is the problem.

Is there anyone out there who has used an Access backend that can be updated by a user to any Access database they want. This is driving me crazy and I need the answer by tomorrow COB.

Thanks for your help!

08-09-2004, 11:33 AM
In my apps that I want to have multiple Access .MDB files, I use the CommonDialog control in order to allow the users to select [the path to] the .MDB, and then I store that in the registry using the SaveSetting() and GetSetting() functions. Or, you can store this info into a regular ol' text file. Then, when the application opens the database, you have the DataSource value.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum