temporary tables in DAO

G

Guest

Does anyone know how to create a temporary table in Access DAO? I have a
complicated data entry form that requires a temp table be created and then
update the underlying tables before closing. The actual record source is
from a make table; however, I need this make table to be - somehow - my temp
table.

I have created the module below and, I guess you would say, call the
procedure in the event property of the On Open of the subform that is
actually being edited but it doesn't work:

Option Compare Database
Public Sub CreateAnMDB()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = DBEngine.CreateDatabase(CurrentProject \ Documents And Settings
\ tmcateer \ MyNewMDB.MDB, dbLangGeneral)
CreateTableDef ("MyNewTable")
Set tdf = db.CreateTableDef("MyNewTable")
Set fld1 = tdf.CreateField("UCh_text", dbMemo)
Set fld2 = tdf.CreateField("SFY", dbLong)
Set fld3 = tdf.CreateField("SAC_num", dbByte)
Set fld4 = tdf.CreateField("SAC_name", dbText, 50)
Set fld5 = tdf.CreateField("UC_grp_name", dbText, 100)
Set fld6 = tdf.CreateField("UC_name", dbText, 125)
Set fld7 = tdf.CreateField("UCh_num", dbLong)
Set fld8 = tdf.CreateField("UCh_sort_num", dbLong)
Set fld9 = tdf.CreateField("UR_name", dbText, 100)
Set fld10 = tdf.CreateField("bp_status", dbText, 20)
Set fld11 = tdf.CreateField("UCh_amount", dbCurrency, 20)
tdf.Fields.Append fld
db.TableDefs.Append tdf

End Sub

I've looked at along of info and I'm getting conflicting feedback on
actually how it's done. One thing for sure, I know I need a temp table in
DAO. If anyone know how to create this code and how it works, I sure would
appreciate your expertise.

Thanks.

Tracy
 
D

Douglas J. Steele

Public Sub CreateAnMDB()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

' You need a string for the database name
Set db = DBEngine.CreateDatabase("C:\Documents And
Settings\tmcateer\MyNewMDB.MDB", dbLangGeneral)
CreateTableDef ("MyNewTable")
Set tdf = db.CreateTableDef("MyNewTable")
' No need to have separate fld1, fld2, fld3.
' If you DO have them, then you need to declare
' each one.
Set fld = tdf.CreateField("UCh_text", dbMemo)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SFY", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_num", dbByte)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_name", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_grp_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_name", dbText, 125)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_sort_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UR_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("bp_status", dbText, 20)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_amount", dbCurrency, 20)
tdf.Fields.Append fld
db.TableDefs.Append tdf

End Sub

If you're creating a temporary table, you're better off creating it in a
temporary database, to avoid bloating in your real database. Tony Toews has
an example at http://www.granite.ab.ca/access/temptables.htm

-
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
G

Guest

Douglas, How do I create a string for the database name? I copied in the
code as you said but I get a compile error/syntax error on the " Set db =
DBEngine.CreateDatabase("C:\Documents And"

Also, I copied the Temp DB already as well which is named Temp DB and the
table is named f_PrimaryEntry. I only kept the table and module correct?
So, I just link my make table to this Temp table and put in the control
source as the table name in my current database? Am I correct in creating
the code in a module and then creating an event procedure to call the module?
If so, is it correct to write the event procedure "on open" in the subform
as follows?

Private Sub Form_Open(Cancel As Integer)
DoCmd.SetWarnings False
DoCmd.OpenModule "mod_PrimaryEntryFunction,,,stLinkCriteria"

End Sub

Sorry for all the questions but I have worked hard on this and it has been
very challenging to me. I greatly appreciate your help.

My new code looks like this:

Option Compare Database
Option Explicit
Public Sub CreateAnMDB()
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field

Set db = DBEngine.CreateDatabase("C:\Documents And
Settings\tmcateer\Temp DB.MDB", dbLangGeneral)
CreateTableDef ("f_PrimaryEntry")
Set tdf = db.CreateTableDef("MyNewTable")

Set fld = tdf.CreateField("UCh_text", dbMemo)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SFY", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_num", dbByte)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_name", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_grp_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_name", dbText, 125)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_sort_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UR_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("bp_status", dbText, 20)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_amount", dbCurrency, 20)
tdf.Fields.Append fld
db.TableDefs.Append tdf

End Sub

Thanks,

Tracy
 
D

Douglas J. Steele

Looks like you're the victim of word-wrap. The line that starts
DBEngine.CreateDatabase is supposed to be the same line as the one that ends
", dbLangGeneral)

You don't want to use OpenModule: all that does is open the module in the
editor.

To call your sub, you can either use simply

CreateAnMDB

or you can use

Call CreateAnMDB()
 
G

Guest

I'm getting a compile error - sub or function not defined on the words
CreateTableDef. I'm not sure why...

Also, will you please check me just to make sure that I've put everything
where it goes....
*The code below is in a module named Temp.
*I created an event procedure "On Open" in the subform and the record source
of the subform is set to the linked table f_PrimaryEntry in my current
database that is actually created in another database named Temp DB that
houses the f_PrimaryEntry. The on open procedure is: Public Sub
Form_Open(Cancel As Integer)
Call CreateAnMDB
End Sub


The code in the module now looks like this:

Option Compare Database

Option Explicit
Public Sub CreateAnMDB()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Set db = DBEngine.CreateDatabase("G:\Temp\Temp DB.MDB", dbLangGeneral)
CreateTableDef ("f_PrimaryEntry")
Set tdf = db.CreateTableDef("f_PrimaryEntry")
Set fld = tdf.CreateField("UCh_text", dbMemo)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SFY", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_num", dbByte)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_name", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_grp_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_name", dbText, 125)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_sort_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UR_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("bp_status", dbText, 20)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_amount", dbCurrency, 20)
tdf.Fields.Append fld
db.TableDefs.Append tdf

End Sub

Other than my compile error, is it put together right whereas I can now have
multiple users in the form and it's actually creating a temp table on each
open? How is the mdlClipboard actually being implemented in the Temp DB?

Thanks!
 
D

Douglas J Steele

You've got 2 CreateTableDef statements in your code. The first one

CreateTableDef ("f_PrimaryEntry")

is incorrect. You want only the second one:

Set tdf = db.CreateTableDef("f_PrimaryEntry")


I'd question calling the routine in a form's Open event: that means that
every time the form opens, it's going to try and create the database. Since
the database will already exist, you'll get an error.
 
G

Guest

That fixed my compile error. And you're right - I received an error message
that the database already exists. How and where would I go about calling it?
Should it be on the on current event? Is this the proper way to call the
module below?

DoCmd.SetWarnings False
DoCmd.OpenModule "mod_PrimaryEntryFunction,,,stLinkCriteria"

End Sub

Thanks again for your expertise.

Tracy
 
G

Guest

Douglas, From my last message, I tried putting the calling the module from on
current and I received an error so what would be the correct method? Thanks.

Tracy
 
D

Douglas J Steele

You should only need to call the routine once. Putting it in a form event
strikes me as completely unnecessary: have a command button that allows you
to call the code.

You might also consider using code like the following, as it will ensure
that the database doesn't already exist.

Public Sub CreateAnMDB()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strFile As String

strFile = "G:\Temp\Temp DB.MDB"

If Len(Dir(strFile)) > 0 Then

Set db = DBEngine.CreateDatabase(strFile, dbLangGeneral)
CreateTableDef ("f_PrimaryEntry")
Set tdf = db.CreateTableDef("f_PrimaryEntry")
Set fld = tdf.CreateField("UCh_text", dbMemo)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SFY", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_num", dbByte)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_name", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_grp_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_name", dbText, 125)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_sort_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UR_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("bp_status", dbText, 20)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_amount", dbCurrency, 20)
tdf.Fields.Append fld
db.TableDefs.Append tdf

Else

MsgBox strFile & " already exists.

End If

End Sub
 
G

Guest

Douglas, I have a Switchboard that opens to a form named "Home". The Home
form allows the user to click on a selection of a group name and then a year
through the use of code, necessary because of the crosstab pull. After that
selection is made, it opens to a subform in the form f_PrimaryEntryScreen
named Budget Units which is showing the filtered records and in turn allows
the user to click on a record selector and dependant upon the record
selection, gives the corresponding information associated with that business
unit in a subform that becomes visible after the selection is made, to the
adjacent sub form f_PrimaryEntryScreen. That f_PrimaryEntry is the editable
screen, thus my reason for needing the temp table. I don't know where or how
I could implement a command button to call the function. Do you in my
particular scenario? That's why I trying to, some how, code it to take place
when that f_PrimaryEntry subform opens upon the selection in the Budget Units
subform on the f_PrimaryEntryScreen.

I SO appreciate your expertise. Thanks again!
 
D

Douglas J. Steele

Well, the code I gave you earlier now won't create a new database if it
already exists, so maybe you should just go with that.
 
G

Guest

Well,..where do I call the code from the f_PrimaryEntry? On which event
procedure should I place it? On open or on current doesn't work.... Thanks.

Tracy
 
D

Douglas J Steele

What does "doesn't work" mean? The only error you've ever mentioned is that
the database already exists.
 
G

Guest

Douglas, I need to call the sub right? So, I added to my f_PrimaryEntry
subforms "On Open" event

Public Sub Form_Open(Cancel As Integer)
CreateAnMDB

End Sub

If calling the sub on the on open event is incorrect because of it creating
the db each time - where then do I call it from? A command button isn't
going to work because of the way it is set up. If I added a command button
to the f_PrimaryEntry form, that would leave room for error, It should
happen behind the scenes, right?

Remember when I was getting a compile error because I incorrectly had 2
CreateTableDef statements in my code? Well, when I take that out and call
the sub as above, I get the database already exists error message again.

I do want every time someone opens the form for it to create a temporary
file for them and then delete that file after the updates have been made in
my other tables.

Thanks,

Tracy
 
D

Douglas J. Steele

Have you tried the "new" code I gave, which checks whether or not the MDB
file exists before it creates the database?
 
G

Guest

Yes, I'm using the new code. I added the call to the on open event and it
gives me the error message. And like I said, I've got to have a temp table
each time someone opens it.

Thanks for your help with this.
 
D

Douglas J Steele

Please post the exact code you're using, as well as the exact error message
you're getting.
 
G

Guest

Douglas, The error message I am receiving is "Run-time error '3204':
Database already exists.

My module is named "Temp" and the code is shown below:
Option Compare Database

Option Explicit
Public Sub CreateAnMDB()

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strFile As String

strFile = "G:\Temp\Temp DB.MDB"

If Len(Dir(strFile)) > 0 Then

Set db = DBEngine.CreateDatabase(strFile, dbLangGeneral)
Set tdf = db.CreateTableDef("f_PrimaryEntry")
Set fld = tdf.CreateField("UCh_text", dbMemo)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SFY", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_num", dbByte)
tdf.Fields.Append fld
Set fld = tdf.CreateField("SAC_name", dbText, 50)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_grp_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UC_name", dbText, 125)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_sort_num", dbLong)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UR_name", dbText, 100)
tdf.Fields.Append fld
Set fld = tdf.CreateField("bp_status", dbText, 20)
tdf.Fields.Append fld
Set fld = tdf.CreateField("UCh_amount", dbCurrency, 20)
tdf.Fields.Append fld
db.TableDefs.Append tdf

Else

MsgBox strFile & " already exists."

End If

End Sub

I call the function on the f_PrimaryEntry subform in the "On Open" event
procedure as shown below:

Public Sub Form_Open(Cancel As Integer)
CreateAnMDB

End Sub

I have the control source set on the f_PrimaryEntry form to f_PrimaryEntry
which is linked to the Temp DB.

Thanks again for your help!

Tracy
 
D

Douglas J. Steele

I'm sorry: it's my fault.

If Len(Dir(strFile)) > 0 Then

should be

If Len(Dir(strFile)) = 0 Then

Len(Dir(strFile)) will return a string if the file exists, or a zero-length
string if it doesn't.
 
G

Guest

Douglas, Excellent... It's working now. After I corrected below, I
received an ambiguous name detected and put in the code:
Public Sub MySubForm_Open(Cancel As Integer)
MyModule.CreateAnMDB = "This is a module-level variable"
Dim MyModule As String
MyModule = "This is the procedure-level variable"
Debug.Print MyModule
Debug.Print MyModule.CreateAnMDB
End Sub

I tried to put it as an event on open but it keeps moving the code to just
above the on open. It seems to be working right. Would that be correct?

THANKS A TON FOR YOUR EXPERTISE!

Tracy
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

How add an autoincrement GUID field by code? 4

Top