?Examples to create new .mdb file and its tables from code (and more)

[paul.schrum]

Can anyone direct me to an example code page which will help me do the
following:

After getting a path and filename from the user, I want to create a
new .mdb file, create tables in the new file without leaving the .mdb
file I am in, then link to the tables so I can populate them.

Also, a different question but very closely related, in my various and
sundry books on programming Access I have never seen a way
programmatically to create a lookup field in a table. Does anyone
have a how-to on that question as well?

Thanks.

- Paul Schrum

 

[Dirk Goldgar]

In

Can anyone direct me to an example code page which will help me do the
following:

After getting a path and filename from the user, I want to create a
new .mdb file, create tables in the new file without leaving the .mdb
file I am in, then link to the tables so I can populate them.

Also, a different question but very closely related, in my various and
sundry books on programming Access I have never seen a way
programmatically to create a lookup field in a table. Does anyone
have a how-to on that question as well?

Thanks.

- Paul Schrum

Create a database, open it, and get a reference to it:

Dim strNewDBPath As String
Dim dbNew As DAO.Database

strNewDBPath = ... ' whatever

Set dbNew = DBEngine.CreateDatabase(strNewDBPath, dbLangGeneral)

You can create tables in the database in a number of ways. You can
execute a CREATE TABLE query:

dbNew.Execute _
"CREATE TABLE People " & _
"(FirstName CHAR, LastName CHAR, " & _
"DateOfBirth DATETIME, " & _
"CONSTRAINT MyTableConstraint UNIQUE " & _
"(FirstName, LastName, DateOfBirth));"

Or you can use DAO methods, as in this example adapted from the online
help:

Dim tdfNew As TableDef

Set tdfNew = dbNew.CreateTableDef("People")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' database.
.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("DateOfBirth", dbDate)
End With

' Append the new TableDef object to the database.
dbNew.TableDefs.Append tdfNew

Or you could use the DoCmd.TransferDatabase method to copy the structure
of an existing table to the new database.

To link to a table in the new database, you can use the
DoCmd.TransferDatabase method:

DoCmd.TransferDatabase _
acLink, _
"Microsoft Access", _
strNewDBPath, _
acTable, _
"People", _
"People"

I'm not sure offhand whether you have to close the database object,
dbNew, that you just created, before you can use TransferDatabase on it.

Also, a different question but very closely related, in my various and
sundry books on programming Access I have never seen a way
programmatically to create a lookup field in a table. Does anyone
have a how-to on that question as well?

I'm confident it can be done, but I'd recommend against it. There are
lots of good arguments against using lookup fields in tables. Sure,
have a field that is the foreign key to another table, but do your
lookups on forms, not in the table itself.

 

[paul.schrum]

Create a database, open it, and get a reference to it:

Dim strNewDBPath As String

<<snip>>

Thanks. I will work on this this weekend.

I'm confident it can be done, but I'd recommend against it. There are
lots of good arguments against using lookup fields in tables. Sure,
have a field that is the foreign key to another table, but do your
lookups on forms, not in the table itself.

Hmm. That's interesting. I just assumed it would be the recommended
approach. In an application I wrote a few years ago (my only major
project in Access), I enforced referential integrity in code. I was
building experience, and I did not have a guru to consult in my
office, so I always wondered if I was doing it the best way. Is that
what you mean by "do your lookups on forms" or something else?

- Paul

 

[Dirk Goldgar]

In

Hmm. That's interesting. I just assumed it would be the recommended
approach. In an application I wrote a few years ago (my only major
project in Access), I enforced referential integrity in code. I was
building experience, and I did not have a guru to consult in my
office, so I always wondered if I was doing it the best way. Is that
what you mean by "do your lookups on forms" or something else?

We'd better clarify our terminology. By "lookup fields", I mean the
combination of data definition, relationship definition, and combo-box
UI created in Access table design view by the "Lookup Wizard". I do
*not* mean defining a field that is the key to a many-to-one
relationship between two tables, such that values for that field in the
"many" table should be "looked up" in the "one" table.

There are three main problems with the lookup fields created by the
wizard:

1. Assuming that this is a table-lookup, not just a value list, the
wizard creates a *hidden* relationship between the tables. It's much
better to explicitly define the relationship using the relationship
designer. Then you have control of it, and can set its properties --
especially those concerned with referential integrity.

2. On the (hopefully) rare occasions when you open the table as a
datasheet, the actual content of the field is hidden from you. Instead,
you see the "looked up" value. And if the relationship has been broken,
so that the foreign key value no longer appears in the lookup table, you
see nothing at all. This makes it harder to investigate problems.

3. Use of the lookup field in the table design is a user-interface
mechanism that encourages you to do your productive database work in
table datasheets. But forms are much better for this purpose. They are
designed to provide a powerful, flexible, and programmable user
interface, and they do a good job at it. So I'd recommend against using
table datasheets for data presentation and editing in production.

Now, none of that is intended to say that the lookup, as provided by the
combo box control, isn't a terrific user-interface feature. But I'd
argue that it should be used on forms, not in the table design itself.
So if I have a many-to-one relationship between two tables, and want
users to choose a value for the "many" table from a list provided by the
"one" table, I'll set about it like this:

First, I'll create both tables. I won't define an index on the
foreign-key field, because I know that the relationship I'm about to
design will create one.

Next, I'll open the Relationships window, add the tables, and define the
relationship between them.

Then I'll create the forms for editing these tables. In the form for
editing the many-side table, I'll use a combo box to display the
foreign-key field, with its rowsource set to a SQL statement that pulls
the appropriate values and their descriptions from the one-side table.
So the combo box will usually display the descriptive text for the item,
not its key. Very simple, obvious lookup values may not require any
description. I may also include some other fields in the SELECT
statement, to appear in the combo box's list, if I think there'll be a
use for them. I'll set the other properties of the combo box
appropriately.

When creating a report of this table, I'll base the report on a query
that joins the two tables and includes the specific fields I want to see
on the report. I don't need to use combo boxes on reports.

 

[paul.schrum]

InCreate a database, open it, and get a reference to it:

Dim strNewDBPath As String
Dim dbNew As DAO.Database

strNewDBPath = ... ' whatever

Set dbNew = DBEngine.CreateDatabase(strNewDBPath, dbLangGeneral)

You can create tables in the database in a number of ways. You can
execute a CREATE TABLE query:

dbNew.Execute _
"CREATE TABLE People " & _
"(FirstName CHAR, LastName CHAR, " & _
"DateOfBirth DATETIME, " & _
"CONSTRAINT MyTableConstraint UNIQUE " & _
"(FirstName, LastName, DateOfBirth));"

Dirk,

I was able to get as far as the quoted text above and it works fine.
Thanks a bunch. No need to reply.

- Paul

 

[paul.schrum]

On Aug 24, 1:58 am, "Dirk Goldgar" <[email protected]>
wrote:

To link to a table in the new database, you can use the
DoCmd.TransferDatabase method:

DoCmd.TransferDatabase _
acLink, _
"Microsoft Access", _
strNewDBPath, _
acTable, _
"People", _
"People"

Dirk,

I have used the above code snippet (which suggested) with success.
Now I would like to unlink those tables just before I exist the
database, but I don't see which DoCmd command does that. Can you
help?

- Paul Schrum