Code not linking to a BE table

[Ben]

All -

I have a BE database, in it, there is a table which contains a record for
every table that is the BE database, except it self. In the front end
database, I have the code to look up any table in this look up table that the
front end does not have a link to. All the front end tables are linked to
the back end and when there is a new back end table, the look up table in the
back end database would contain an entry.

so each time when the front end opens, it scans this look up table in the
back end to see which table it doesn't already have. And if it is not part
of the front end's table def, it adds the link to the back end.

Below is the code I use to scan the back end and add new links for any new
back end table that is not already part of the front end links in the front
end table def.

----------------------------------------------------------------------------

Sub test()

Dim dbBE As DAO.Database
Dim dbFE As DAO.Database
Dim tdfFE As TableDef
Dim tdfsFE As TableDefs
Dim rsDAO As DAO.Recordset
Dim strSQL As String
Dim str_BE_tbl As String
Dim bTableExist As Boolean


Set dbBE = OpenDatabase(strBE_FilePath)
Set dbFE = CurrentDb
Set tdfsFE = dbFE.TableDefs
strSQL = "SELECT _BE_tblCode FROM tbl_bh__BE_tblStatus"
Set rsDAO = dbBE.OpenRecordset(strSQL)

rsDAO.MoveLast
rsDAO.MoveFirst


While Not rsDAO.EOF
str_BE_tbl = rsDAO("BE_tblCode")
Debug.Print str_BE_tbl
For Each tdfFE In tdfsFE
If (tdfFE.Name = str_BE_tbl) Then
bTableExist = True
Exit For
End If
Next

If Not (bTableExist) Then
Set tdfFE = dbFE.CreateTableDef("JetTable")
tdfFE.Connect = ";DATABASE= """ & strBE_FilePath & """"
tdfFE.SourceTableName = str_BE_tbl
dbFE.TableDefs.Append tdfFE
End If
bTableExist = False
rsDAO.MoveNext
Wend
End Sub
----------------------------------------------------------------------------

Everytime, when the codes executes the line:
dbFE.TableDefs.Append tdfFE

It gives me a: Run-time error '3055': Not a valid file name.
what I don't get is, this is the same code that was suggested to me earlier
and it is almost the same as the on line help file for the Connect and
SourceTablename Properties example.

Thanks for sharing your thoughts and I am hoping an extra few pairs of eyes
will spot my syntax error.

Thanks,

Ben


--

 

[Douglas J. Steele]

As far as I know, you do not need quotes around the connection string, so
that this should be sufficient:

tdfFE.Connect = ";DATABASE=" & strBE_FilePath

(note that there are no spaces around the equal sign)

Another problem is that you're always naming your new linked table JetTable.
That'll only work once!

Try

Set tdfFE = dbFE.CreateTableDef(str_BE_tbl)

 

[Ben]

Doug,

Got it. Thanks.

Ben



--

As far as I know, you do not need quotes around the connection string, so
that this should be sufficient:

tdfFE.Connect = ";DATABASE=" & strBE_FilePath

(note that there are no spaces around the equal sign)

Another problem is that you're always naming your new linked table JetTable.
That'll only work once!

Try

Set tdfFE = dbFE.CreateTableDef(str_BE_tbl)