Make Table in Back End using VBA code is not creating the table

[Hugh self taught]

Hi Guys & Gals,

The knowledge & helpfulness on this forum never fails to amaze me.

My problem is thus:-

I have found several ways to create a table in a back end via code but the
following code I still can't get to work. I would like to so I can use it in
a small project I'm doing for myself & then be able to use it in future
developments when suitable.

It is a combination of various code I've
found on the forum but I'm missing something because the Debug.Print only
shows the BE path in the
immediate window & the table is not created.

Dim dbs As Database
Dim tdf As TableDef
Dim fldNew As Field
Dim ind As Index

Set dbs = OpenDatabase(strBackendPath)
Debug.Print dbs

Set tdf = dbs.CreateTableDef(strNewCplsTbl)
Debug.Print tdf

tdf.Connect = ";DATABASE=" & strBackendPath & ""
Debug.Print tdf.Connect

tdf.SourceTableName = strNewCplsTbl
'db.TableDefs.Append tdf

Debug.Print tdf.SourceTableName

With tdf
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of your database.

'AutoNumber: Long with the attribute set.
Set fldNew = .CreateField("CoupleID", dbLong)
fldNew.Attributes = dbAutoIncrField + dbFixedField
..Fields.Append fldNew
..Fields.Append .CreateField("CoupleNumber", dbText, 4)
..Fields.Append .CreateField("MaleID", dbLong)
..Fields.Append .CreateField("FemaleID", dbLong)
..Fields.Append .CreateField("Ballroom", dbBoolean)
..Fields.Append .CreateField("Latin", dbBoolean)
..Fields.Append .CreateField("Active", dbBoolean)
..Fields.Append .CreateField("StartDate", dbDate)

End With
dbs.TableDefs.Append tdf
CurrentDb.TableDefs(strNewCplsTbl).RefreshLink

Set tdf = Nothing
Set dbs = Nothing
Set fldNew = Nothing
Set ind = Nothing

dbs.Close

 

[Douglas J. Steele]

You seem to be trying to use tdf for two different things: both the table in
the back-end and the linked table in the front-end.

Create the table in the back-end first (which means removing the tdf.Connect
= ";DATABASE=" & strBackendPath & "" line of code, as well as
CurrentDb.TableDefs(strNewCplsTbl).RefreshLink ).

Then, create the table in the front-end (using a brand-new Create TableDef
statement and all the rest)

 

[Hugh self taught]

Amazing what another pair of eyes does. Thanks Douglas.

Now that I see that, should I not just change the tdf to read dbs in these
lines since I'm trying to create the table in the back end?

tdf.Connect = ";DATABASE=" & strBackendPath & ""
tdf.SourceTableName = strNewCplsTbl

Would this line then also still be correct?
CurrentDb.TableDefs(strNewCplsTbl).RefreshLink

 

[Douglas J. Steele]

Not sure I understand your question.

When creating the table in the back-end, you'd refer to dbs.

When creating the table in the front-end, you'd need to instantiate an
instance of the front-end database (you can't use CurrentDb instead of dbs:
you need to use Set dbs = CurrentDb and then create the TableDef object)