This checks a table to see if it is linked or actual, and returns the path of the database with the actual table. I left in the commented code from the original.
Function getBackEndString(tblName) As String
Dim db As Database
Dim DbPath As Variant
Dim pathToSend As String
'get back end path of linked table
DbPath = DLookup("Database", "MSysObjects", "Name='" & tblName & "' And Type=6")
If IsNull(DbPath) Then
pathToSend = CurrentDb.Name
'Set Db = CurrentDb 'if local table
'Set Db = OpenDatabase(DbPath) 'if linked table
pathToSend = DbPath
'If Err <> 0 Then
'failed to open back end database
' Exit Function
'in case back end has different table name than front end
tblName = DLookup("ForeignName", "MSysObjects", "Name='" & tblName & "' And Type=6")
getBackEndString = pathToSend
Then I call it with a table I know will be in the underlying database or the one I want to use. The code below includes the beginning of it doing things with the back end.
Dim cnn As adodb.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column
Dim beStr As String
beStr = getBackEndString("Work")
'check if real table is in FE or BE
If beStr = CurrentDb.Name Then
Set cnn = CurrentProject.Connection
Set cnn = New adodb.Connection
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & beStr
'Set connection and catalog to current database.
'Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn
Set col = cat.Tables(strTbl).Columns(strCol)
col.Properties("Seed") = lngSeed
You might need to change the Provider to 4.0 or something. It was a pain to find the 12.0 syntax that works with mine.
The simple solution would be to put in the back end's path, but it would break if you move it or link to multiple tables. I hope this works in your case!