CREATE TABLE within VBA (2 Viewers)

Agnister

Registered User.
Local time
Today, 22:05
Joined
Jul 2, 2011
Messages
21
I want to create a temporary table with the SQL "CREATE TABLE" option within VBA and use the Execute command instead of DoCmd.RunSQL. I just want the Table structure without any records. The code fails on the Execute command. I can not see anything wrong with this code. Please help
Dim db As DAO.Database
Dim strSQL As String

Set db = DBEngine(0)(0)
strSQL = "CREATE TABLE tbl_ImportTemp (Entered Date DATE,Transactions TEXT, " & _
" Amount CURRENCY, Balance CURRENCY, Category TEXT, Cost_Code NUMBER );"
db.Execute (strSQL)
Set db = Nothing


:banghead:



 

GinaWhipp

AWF VIP
Local time
Today, 07:05
Joined
Jun 21, 2011
Messages
5,901
Try...

Code:
Dim db As DAO.Database
Set db = DBEngine(0)(0)

db.Execute "CREATE TABLE tbl_ImportTemp (EnteredDate DATE,Transactions TEXT, " & _
"Amount CURRENCY, Balance CURRENCY, Category TEXT, Cost_Code NUMBER );"

db.Close

Note, I also removed the space in *Entered Date*
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 28, 2001
Messages
26,996
If you are going to do this often, don't bother to create and re-create wheels. Build the table once and then when you want to use it, DELETE everything in it, leaving it as an empty table. The table can be permanent but its contents could CERTAINLY be temporary.

Note also that creating your temp table in the (0)(0) workspace means you are creating it in your current DB. (Which is OK.) This simply means that you will be churning up your DB every time you do this. That sort of create/delete behavior eventually will lead to DB bloat, requiring an occasional Compact & Repair operation. Again, not a terrible thing - but something you should be aware of.
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:05
Joined
Sep 21, 2011
Messages
14,038
If you must have spaces in your field names, enclose them with [ & ]

Code:
CREATE TABLE tbl_ImportTemp ([Entered Date] DATE,Transactions TEXT, Amount CURRENCY, Balance CURRENCY, Category TEXT, Cost_Code NUMBER );

All I did was paste that into the SQL window and try and run, and it highlighted the second date, as it already had a field called Entered with a type of date.?

Personally I would use EnteredDate

I want to create a temporary table with the SQL "CREATE TABLE" option within VBA and use the Execute command instead of DoCmd.RunSQL. I just want the Table structure without any records. The code fails on the Execute command. I can not see anything wrong with this code. Please help
Dim db As DAO.Database
Dim strSQL As String

Set db = DBEngine(0)(0)
strSQL = "CREATE TABLE tbl_ImportTemp (Entered Date DATE,Transactions TEXT, " & _
" Amount CURRENCY, Balance CURRENCY, Category TEXT, Cost_Code NUMBER );"
db.Execute (strSQL)
Set db = Nothing


:banghead:



 

Cronk

Registered User.
Local time
Today, 22:05
Joined
Jul 4, 2013
Messages
2,770
Further to Doc's reply in #3.
Where I have had to resort to creating/deleting temporary tables (or deleting contents multiple times), I always have a separate accdb to hold these tables and always on the users' local PCs.

I avoid excessive bloat by deleting/recreating the whole temporary accdb periodically say every 3 months or every 100 logons by a user.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 06:05
Joined
Feb 28, 2001
Messages
26,996
Cronk, the easier solution for beginners is to make that secondary (empty) database and use a "launcher script" to just delete the current copy, then copy the secondary database to where you needed it. That way, if you are going to use a front-end and a side-end, you can make fresh copies of both.

Agnister, you can also do the copy I was talking about from VBA by using Kill to remove the old copy and FileCopy to make a new one, then open the secondary database. You are already using a prefix to qualify the location of the tables, so that would be no big deal.

Kill - https://docs.microsoft.com/en-us/office/vba/language/reference/user-interface-help/kill-statement

Filecopy - https://docs.microsoft.com/en-us/of...erence/user-interface-help/filecopy-statement

Open (database) - https://docs.microsoft.com/en-us/of...se-reference/dbengine-opendatabase-method-dao
 

Cronk

Registered User.
Local time
Today, 22:05
Joined
Jul 4, 2013
Messages
2,770
Doc, I like that description - "side-end".
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:05
Joined
Feb 19, 2002
Messages
42,970
I use "side-ends" frequently at one client site. All their data comes as downloads from another company that does their data processing and my people are all analysists The downloads are junky and inconsistent so I fix up the data after it is downloaded so they can join it to other local databases. Since the data changes daily, there is no way to actually avoid this. We are looking into interacting with an Azure data warehouse but we still have lots of issues with the data and the current method may end up being less of a problem unless I can make some headway on making the local databases match the remote data values. We don't have much control over what the third-party stores since the data ultimately comes via standard transaction sets.

I generally have multiple tables so I define a template database with all the tables defined, related and empty. I compact the empty db and that is the master. Each time the user needs to start a new analysis, they push a button which deletes the old copy of the side-end and copies the template and replaces the last used copy. The links all stay the same because the copy of the template never changes. Then the code runs the imports and any fix up queries to standardize the codes that they will need to join on.
 

Agnister

Registered User.
Local time
Today, 22:05
Joined
Jul 2, 2011
Messages
21
Thank you all as the entire input discussion as it has been very intuitive. Gina Whipp you minor change worked a treat but Doc Man raised an interesting point about "DB Bloat". Something I was not aware of and this escalated the discussion into "Side-ends" and ended with a very interesting approach by Pat Hartman which was familiar to me when I worked with assay data from different laboratories. Thanks again everyone.
 

Users who are viewing this thread

Top Bottom