Create new table in backend

Question

My application uses ACCDE front end and ACCDB back end. It has been distributed to various locations (resides on single computer in each location). I´ve received a feature request that I´d like to accomodate but it requires the creation of several new tables.

• How do I formulate the CREATE TABLE syntax to make sure the new tables are created in the back end?
• Can I link the new tables in the front end via code? I´m a neophyte so if via code is too difficult or involved I can just do it the old fashioned way via the menu.

Thanks!

Answer 1

To create a table:

Dim dbs As DAO.Database

Dim strSQL As String

Set dbs = OpenDatabase("C:\Databases\FiNDeM_BE.accdb")

strSQL = "CREATE TABLE tblNew (ClientID INTEGER PRIMARY KEY, LastName CHAR (30), DateOfBirth DATETIME)"

dbs.Execute strSQL, dbFailOnError

dbs.Close

Answer 2

And to link:

DoCmd.TransferDatabase TransferType:=acLink, DatabaseType:="Microsoft Access", _

  DatabaseName:="C:\Databases\FiNDeM_BE.accdb", _

  Source:="tblNew", Destination:="tblNew"

Answer 3

Awesome!!!!