Link front end db to back end table

R

Rod

I'm trying to link a front end db to a table in a back end db. This is my
code...

'Creates link to table SpringfieldLetters in smpdata.mdb
Function fncLinkToSpringfieldLetters()
Dim strInternalTableName As String
Dim strExternalDB As String
Dim strExternalTable As String
Dim tdf As TableDef
Dim db As Database

Set db = CurrentDb()
strInternalTableName = "SpringfieldLetters"
strExternalTable = "SpringfieldLetters"
Set tdf = db.CreateTableDef(strInternalTableName)

tdf.Connect = ";DATABASE=" & "c:\lacc\smpdata.mdb"
tdf.SourceTableName = strExternalTable
db.TableDefs.Append tdf

End Function

When I run the function I receive the vb run-time error 3012: Object
"SpringfieldLetters" already exists.

If I change the name of the linked table to a distinct name, it works fine.
However, I want the linked table to have the same name as the back end table.

I tried a workaround...

DoCmd.CopyObject , "SpringfieldLetters", acTable, "SpringfieldLetters2"

trying to rename the linked table with the distinct name
"SpringfieldLetters2" to the name of the table in the back end db. I
received the vb run-time error 2501: The CopyObject action was canceled.

I'd appreciate your help!

Rod
 
R

Rod

Alex,

The table doesn't exist in the front end db. That's what's confusing to me.
I'll give a try.

Rod
 
R

Rod

Alex,

I inserted
db.TableDefs.Delete(strInternalTableName) before line
Set tdf = db.CreateTableDef(strInternalTableName)

I receive this vb run-time error 3265: Item not found in this collection.

How can a table be deleted if it doesn't exist?

Rod
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Link front end db to back end table, part 2 1
VBA-Table Word 2019 - Move a selected text, from a cell to the adjacent cell to its right, by pressing the ENTER key 0
TOO MANY BACK-END LINKS 4
TOO MANY BACK END LINKS 2
TOO MANY LINKS TO BACK-END 14
Unexplained failure of wdFindStop at end of search 0
Change one table link by vba 1
make table in back end, then link 4

Top