How to creat a new table to the back end (BE) from the front end (

[Ben]

Hi all -

Environment: XP, Access 2003.
I have a front end (FE) but I need to create a table to the back end (BE).
I just realized the code I use to create the table on creates to the front.
I need to create a table in BE and have the code(from the FE) to link it back
to the FE.

Thanks for your help.

Ben

--

 

[Brian]

Dim FilePath as String
FilePath = C:\MyPath\MyDB.mdb"
Dim db as DAO.Database
Dim tdf As DAO.TableDef
Set db = DBEngine.Workspaces(0).OpenDatabase(FilePath, True)
Set tdf = db.CreateTableDef(tblName)

This creates the table. Once you open the connection to the BE using the Set
db = statement above, you also have access to all the other collections
within that DB (Fields, Indexes, etc.). For example, to then access fields
for a table in the BE:

Dim fld as DAO.Field ' in addition to other variables above.
Set db = DBEngine.Workspaces(0).OpenDatabase(FilePath, True)
Set tdf = db.TableDefs(tblName)
Set fld = tdf.CreateField("MyField",DBLong)

 

[Brian]

Follow-up

1. When I need to create a new table in a BE, I use a separate patch file
(MDB), not the FE. I create the table manually in the patch file, then write
the code to simply copy it from the patch file to the BE, rather than
creating the table programmatically. Then I add code to create any necessary
relationships between the new table and pre-existing tables.
2. When I need to add a link to a new BE table in the FE, I add the link
manually, then just issue a new FE that includes the new table link. My FE's
all include code that allow the user to browse for the relative location of
the BE and refresh links so that the FE/BE locations can be different
relative locations for the users than for me as a developer.

 

[Ben]

Brian,

Thanks for the tip. Looking at the code, I am wondering if the FE's table
windows will show the new link to the new BE table I created?

Thanks,

Ben

 

[Brian]

Sorry. That was a partial answer. You need to add the link also. Here is the
code I use this:

Dim FilePath as String
FilePath = "C:\MyPath\MyDB.mdb"
Dim db as DAO.Database
Dim tdf as DAO.TableDef

Set db = DBEngine.Workspaces(0).OpenDatabase(FilePath, True)
Set tdf = db.CreateTableDef(tblName)
tdf.Connect = ";DATABASE=" & FilePath & ""
tdf.SourceTableName = tblName
db.TableDefs.Append tdf

This appends the link to the FE. As I said in my prior follow-up post,
though, if you are distributing a new FE anyway, why not add the link
yourself before distributing it rather than writing code to create the link
at runtime? Just ensure the user has a way to refesh the link should his
FE-to-BE relative path be different from yours.