Create Table in Linked Database

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a separate database for my data and my application that i have linked
when the program starts up. I've been doing this so that people can have new
versions of the application without having to delete all of their entered
data.

I am making a new release of the application that requires some alterations
to the database. Altering the tables in the other database shouldn't be a big
deal, however i also need to create a table there...

Is there a way to do a create table statement in the application database
that will put the table in the linked database as opposed to the application
database? After it's created i will have no problem linking it, my problem is
the create table statement automatically puts it in the database the sql is
being run from (the application database).

Thanks,
-Ben
 
Here's an example from one of my one apps ...

First I get the Connect property of one of the linked tables in the
'front-end' application MDB ...

strDataFile = CurrentDb.TableDefs("tblAbsenceReason").Connect

Now extract the path and file name from the Connect property ...

strDataFile = Mid$(strDataFile, InStr(1, strDataFile, "=") + 1)

Get a reference to the 'back-end' data MDB using the above path and file
name ...

Set db = DBEngine.OpenDatabase(strDataFile, True)

Create the new table in the data MDB ...

Set tdf = db.CreateTableDef("tblNEWBReason")
Set fld = tdf.CreateField("NEWBCode", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("NEWBDescrip", dbText, 50)
tdf.Fields.Append fld
Set fld = Nothing
db.TableDefs.Append tdf

Add a primary key index to the new table ...

Set idx = tdf.CreateIndex("PrimaryKey")
idx.Primary = True
idx.Fields.Append idx.CreateField("NEWBCode")
tdf.Indexes.Append idx
Set idx = Nothing
Set tdf = Nothing

Now, in the application MDB, create the link to the new table in the data
MDB ...

Set tdf = CurrentDb.CreateTableDef("tblNEWBReason")
tdf.Connect = ";DATABASE=" & strDataFile
tdf.SourceTableName = "tblNEWBReason"
CurrentDb.TableDefs.Append tdf
 
BenWeber said:
I have a separate database for my data and my application that i have linked
when the program starts up. I've been doing this so that people can have new
versions of the application without having to delete all of their entered
data.

I am making a new release of the application that requires some alterations
to the database. Altering the tables in the other database shouldn't be a big
deal, however i also need to create a table there...

Is there a way to do a create table statement in the application database
that will put the table in the linked database as opposed to the application
database? After it's created i will have no problem linking it, my problem is
the create table statement automatically puts it in the database the sql is
being run from (the application database).

Thanks,
-Ben
Click to expand...

You can duplicate the structure of a "local" table (or quite possibly,
the structure of joined tables) in a "remote" database with something
like this. To simultaneously insert some rows write an appropriate WHERE
clause.

SELECT * INTO RemoteTable IN '<path to remote mdb>'
FROM LocalTable
WHERE False;

I'm not sure if there's a CREATE TABLE syntax that does this.
 
Back
Top