ISAM error - seems to be above my paygrade

[GraeagleBill]

Well, I thought this was going to be simple, but I guess not. (In my old vocabulary, ISAM is a mainframe access method) Anyway, I've not been successful in finding a reasonable explanation that fits what I'm trying to accomplish.



strPerDB is global and is the name of an alternate backend DB.

The error:

Code:

Option Compare Database
Option Explicit
Public Sub CCAcctTrans()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Test new function to access CC traffic.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
MsgBox ADDCCAccts
End Sub
Private Function ADDCCAccts() As Boolean
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
'  Connect to the CCTrans table temporarily to see if there's any CC traffic that
'  needs to be recorded.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim tdf As New DAO.TableDef


On Error GoTo LinkError


With CurrentDb


    .TableDefs.Refresh
    
    Set tdf = .CreateTableDef("tblCCTrans")
    tdf.Connect = "tblCCTrans"
    tdf.SourceTableName = strPerDB
    .TableDefs.Append tdf                              <<<<<<<<<<<<<<<< gets the 3170 on this statement
    .TableDefs.Refresh


End With


Set tdf = Nothing


ErrExit:


ADDCCAccts = False
Exit Function


LinkError:
    MsgBox "Error encountered linking to CC Traffic table" & vbNewLine & _
           "Description: " & Err.Description & " Error# = " & Err.Number
           
    GoTo ErrExit
End Function

[Welshgasman]

So how can a sourcetablename be a db and not a table?

[Gicu]

Usually you link another database table using the Docmd.TransferDatabase method; if you want to use CreateTableDef method you need to look closer at its arguments, the Connect string must be the correct connection string for the external database type, for an Access db back-end it would like something like this:

;DATABASE=C:\_Access\DCIS_DB_BE.mdb

So your code should be:

    tdf.Connect = ";DATABASE=" & strPerDB & ""
    tdf.SourceTableName = "tblCCTrans"
    .TableDefs.Append tdf                              <<<<<<<<<<<<<<<< gets the 3170 on this statement
    .TableDefs.Refresh

Cheers,

[GraeagleBill]

I was off on the wrong foot here. And, as I sought to provide a description to further explain, the issue disappeared as I approached the need in a far simpler and appropriate manner. Namely, a permanent link to the table in an alternate DB that remains "unmolested" during a link refresh as the app switches between multiple backends as the app instances require. But, thanks for the heads-up with the Docmd.TransferDatabase method.

[Gicu]