Go Back   Access World Forums > Microsoft Access Discussion > General

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-09-2019, 10:14 PM   #1
Agnister
Newly Registered User
 
Join Date: Jul 2011
Location: Townsville; Queensland; Australia
Posts: 21
Thanks: 8
Thanked 0 Times in 0 Posts
Agnister is on a distinguished road
CREATE TABLE within VBA

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







Agnister is offline   Reply With Quote
Old 01-09-2019, 10:32 PM   #2
GinaWhipp
AWF VIP
 
GinaWhipp's Avatar
 
Join Date: Jun 2011
Location: Ohio, USA
Posts: 5,279
Thanks: 20
Thanked 873 Times in 858 Posts
GinaWhipp has a spectacular aura about GinaWhipp has a spectacular aura about
Re: CREATE TABLE within VBA

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*
__________________
Gina Whipp
Microsoft MVP (Access 2010-2015)


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
GinaWhipp is offline   Reply With Quote
Old 01-10-2019, 07:06 AM   #3
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,124
Thanks: 70
Thanked 1,375 Times in 1,267 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: CREATE TABLE within VBA

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 01-10-2019, 07:42 AM   #4
Gasman
Enthusiastic Amateur
 
Join Date: Sep 2011
Location: Swansea, South Wales,UK
Posts: 3,149
Thanks: 349
Thanked 535 Times in 517 Posts
Gasman has a spectacular aura about Gasman has a spectacular aura about
Re: CREATE TABLE within VBA

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

Quote:
Originally Posted by Agnister View Post
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






__________________
Access novice. Sometimes trying to give something back.
Access 2007


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Gasman is offline   Reply With Quote
Old 01-10-2019, 08:47 PM   #5
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,029
Thanks: 3
Thanked 441 Times in 434 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: CREATE TABLE within VBA

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.
Cronk is offline   Reply With Quote
Old 01-11-2019, 07:03 AM   #6
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,124
Thanks: 70
Thanked 1,375 Times in 1,267 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: CREATE TABLE within VBA

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/off...kill-statement

Filecopy - https://docs.microsoft.com/en-us/off...copy-statement

Open (database) - https://docs.microsoft.com/en-us/off...ase-method-dao
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 01-11-2019, 09:56 AM   #7
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,029
Thanks: 3
Thanked 441 Times in 434 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: CREATE TABLE within VBA

Doc, I like that description - "side-end".

Cronk is offline   Reply With Quote
Old 01-11-2019, 10:06 PM   #8
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,501
Thanks: 13
Thanked 1,442 Times in 1,374 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: CREATE TABLE within VBA

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 01-12-2019, 04:00 AM   #9
Agnister
Newly Registered User
 
Join Date: Jul 2011
Location: Townsville; Queensland; Australia
Posts: 21
Thanks: 8
Thanked 0 Times in 0 Posts
Agnister is on a distinguished road
Re: CREATE TABLE within VBA

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.

Agnister is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Create combobox based table column without messing the table settings up? behedwin General 1 11-16-2017 07:29 AM
insert to a table from another table with create a record in named data macro mana Access Web 11 10-12-2015 08:20 AM
Create a Temp table to create Totals Query Rx_ Code Repository 0 09-26-2012 07:52 AM
Create table, loop one recordset, create two records in new, if criteria met tiberius Modules & VBA 3 04-12-2009 07:02 PM
[SOLVED] How do I...Create records by code in table B when a new record is made in table A??? farmersckn Tables 1 08-06-2004 07:12 AM




All times are GMT -8. The time now is 01:59 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World