Creating a linked table Table

  • Thread starter Thread starter Paul
  • Start date Start date
P

Paul

Some advice please

I need to create a table, define fields and types/properties using VBA to an
application I have already distributed. The database has been split.

Can anyone point me to any code for this and I am also unsure where the
table should be created i.e. front or back end.

Thanks
 
Paul wrote
I need to create a table, define fields and types/properties using VBA to an
application I have already distributed. The database has been split.

Can anyone point me to any code for this and I am also unsure where the
table should be created i.e. front or back end.
Click to expand...


Assuming you are using a Jet back end database and DAO.

Front vs. back depends on how you are using to table. If it
only contains information pertinate to the fromt end system,
then it could be in the front end db. If the data in the
table will be used by multiple front end dbs, then the table
must be in the back end.

You can create the table in the back end by opening the back
end db and executing a Create Table query in the BE db:

Dim dbFE As Database
Dim dbBE As Database
Dim strPath As String

' Get path to BE from any existing linked table
Set dbFE = CurrentDb()
strPath = dbFE.TableDefs("any linked table").Connect
strPath = Mid(strPath, 11)
Set dbFE = Nothing

' Create the table in the BE
Set dbBE = OpenDatabase(strpath)
dbBE.Execure "CREATE TABLE tablename (fields ...), ...", _
dbFailOnError

' Link the FE to the BE
DoCmd.TransferDatabase acLink, "Microsoft Access", _
strPath, acTable, "tablename", "tablename"

Set sbBE = Nothing
 
Back
Top