How to use this funtion

[sandanet]

Hi experts,

Could u please tell me how to use this function:

Public Sub CreateLinkedTable(strLinkedTableName As String _
, strSourceTable As String _
, strConnectString As String)
'Create a linked table

Dim tdf As DAO.TableDef

'Create the linked table
With CurrentDb

'Instantiate the table
Set tdf = .CreateTableDef(strLinkedTableName)

'Set the properties of importance
tdf.Connect = strConnectString
tdf.SourceTableName = strSourceTable

'Append the object to the collection
.TableDefs.Append tdf

End With

'Refesh the navigation pane/db window
RefreshDatabaseWindow

End Sub

The table that i need to re-link with it is table2

Any help would be welcomed,

 

[theDBguy]

Hi. That code is not for "re-linking" an existing table. Instead, it's used to create a "new" linked table. Which one did you want to do?

 

[Ranman256]

link in a new table:
docmd.TransferDatabase acLink ,"Microsoft Access","c:\temp\Mydb.mdb",acTable ,"tblMyTbl","tblMyTbl"

 

[sandanet]

Hi. That code is not for "re-linking" an existing table. Instead, it's used to create a "new" linked table. Which one did you want to do?

Yes I would like to create a new linked table

 

[sandanet]

link in a new table:
docmd.TransferDatabase acLink ,"Microsoft Access","c:\temp\Mydb.mdb",acTable ,"tblMyTbl","tblMyTbl"

This function TransferDatabase acLink doesn't work with access2010

 

[theDBguy]

Yes I would like to create a new linked table

Okay. Unfortunately, that function was probably written for someone who has access to the corresponding connection string, because you need to supply it to the function for it to work. Do you have that information?

Where did you get it? Just curious...

 

[sandanet]

Okay. Unfortunately, that function was probably written for someone who has access to the corresponding connection string, because you need to supply it to the function for it to work. Do you have that information?

Where did you get it? Just curious...

I've found it here

Access 2010 bug with TransferDatabase

social.microsoft.com

 

[sandanet]

Okay. Unfortunately, that function was probably written for someone who has access to the corresponding connection string, because you need to supply it to the function for it to work. Do you have that information?

Where did you get it? Just curious...

My original code is this:

Option Compare Database
Option Explicit
Public CheckLinkDB As Integer

Function AutoLink()
On Error Resume Next
Dim strPath As String

strPath = Application.CurrentProject.Path & "\idcard.mdb"

Dim BackObj As TableDef, BackDB As Database
Set BackDB = DBEngine.Workspaces(0).OpenDatabase(strPath, True, False)
For Each BackObj In BackDB.TableDefs
If Left(BackObj.name, 4) <> "MSys" And BackObj.name = "table2" Then

DoCmd.TransferDatabase acLink, "Microsoft Access", strPath, acTable, BackObj.name, BackObj.name

End If
Next BackObj

Set BackDB = Nothing

If IsTable("table2") = False Then
CheckLinkDB = 1
Else
CheckLinkDB = 0
End If

End Function


It was working correctly on access 2007 but on access 2010 this function DoCmd.TransferDatabase acLink doesn't work

 

[theDBguy]

I've found it here

Access 2010 bug with TransferDatabase

social.microsoft.com

Hi. Thanks for posting the link. What was the answer to my other question about knowing or having the appropriate connection string?

 

[theDBguy]

It was working correctly on access 2007 but on access 2010 this function DoCmd.TransferDatabase acLink doesn't work

What does that mean? Are you getting an error? What does it say?

 

[sandanet]

Hi. Thanks for posting the link. What was the answer to my other question about knowing or having the appropriate connection string?

I've no connection string, i just have two databases and want to link with table2 in the second database

 

[sandanet]

What does that mean? Are you getting an error? What does it say?

Yes, I got a msg says: 7874 Microsoft access unable to find "table2"

 

[theDBguy]

I've no connection string, i just have two databases and want to link with table2 in the second database

Well, without it, you won't be able to use that function. However, it's easy to find if you already have existing linked tables to the same BE.

 

[theDBguy]

Yes, I got a msg says: 7874 Microsoft access unable to find "table2"

That doesn't sound like an issue with the TransferDatabase method. Where is Table2 located?

 

[sandanet]

Well, without it, you won't be able to use that function. However, it's easy to find if you already have existing linked tables to the same BE.

but this code
DoCmd.TransferDatabase acLink, "Microsoft Access", strPath, acTable, BackObj.name, BackObj.name

doesn't work on access2010

is there any other function may u know?

 

[sandanet]

That doesn't sound like an issue with the TransferDatabase method. Where is Table2 located?

table2 located in the second database which is located in the same bath of first database
I just trying to do a normal link with table2 in the second database

 

[sandanet]

That doesn't sound like an issue with the TransferDatabase method

yes, ppl said that too when i googled it

 

[theDBguy]

but this code
DoCmd.TransferDatabase acLink, "Microsoft Access", strPath, acTable, BackObj.name, BackObj.name

doesn't work on access2010

is there any other function may u know?

I can't see your database, but it's hard to believe that method stopped working only in 2010 and still work with 2016.

 

[theDBguy]

table2 located in the second database which is located in the same bath of first database
I just trying to do a normal link with table2 in the second database

I would say the clue is in the error message. Maybe focus on your syntax and see if you're using Table2 in the right argument.

 

[sandanet]

I can't see your database

the code of mine is posted above, the database located here strPath = Application.CurrentProject.Path & "\idcard.mdb"