Linked Tables and VB6

[Chris Mayers]

Hi,

I have the following setup:
On the client, a VB6 app which uses a Jet (Access) database containing
queries, also on the client. This database links to a Jet(Access) database
containg the data which lives on the server.

My question is this: If we need to change the location of the databse on the
server, how can I (programatically) update the 'link locations' of the
tables in the client database(es). You can't assume the MS-Access will exist
on the client.

Thanks,
ChrisM

 

[Naresh Nichani MVP]

Hi :

Try using this function to Links two tables - strShadow is the local
database path, strLinkedPath is the Path to Server database, tblName is name
of Table on Server and new tblName is name of table on client.

Public Function LinkTable(strShadow As String, strLinkedPath As String,
tblName As String, newtblName As String) As Boolean
'NN - 7/27
'returns True if successfull

On Error GoTo errHandler
Dim db As DAO.Database
Dim tdfLinked As DAO.TableDef
Dim visPath As String

Set db = Workspaces(0).OpenDatabase(strShadow)
Set tdfLinked = db.CreateTableDef(newtblName)
tdfLinked.Connect = "MS Access;DATABASE=" & strLinkedPath
tdfLinked.SourceTableName = tblName
On Error Resume Next
db.TableDefs.Delete newtblName
On Error GoTo errHandler
db.TableDefs.Append tdfLinked
LinkTable = True
db.Close
Set db = Nothing

Exit Function
errHandler:
LinkTable = False
Msgbox err.Description
End Function

Regards,

Naresh Nichani
Microsoft Access MVP

 

[ChrisM]

Hi Naresh,

Sorry, I should have also said that I'm using ADO.
Thanks for you post anyway, it got me looking in the right direction, and I
found this:
http://support.microsoft.com/defaul...port/kb/articles/Q240/2/22.ASP&NoWebContent=1
which show me how to do a similar thing in ADO.

Cheers,

ChrisM