Syntax to create table using VBA ADO?

[ossuary]

Hello,

What is the correct syntax to use a Create Table query using VBA ADO? Specifically, I am trying to copy the contents of a table, including all of the column and number formatting, into a new table.

I originally used a "select into" query, but this copied only the data, not the formatting.

I tried using:

CREATE TABLE Table2 AS (SELECT * FROM Table1);

But I get a syntax error that isn't really explained by the help file (it just says "syntax error in Create Table statement").

All I need to do is take one table and copy it in its entirely to a new table, from Excel using the ADO connection. Can anyone help?

Hopefully this is just a syntax problem and it's an easy answer... I like easy answers.

 

[boblarson]

Two things.

1. It is SQL statements you need to use. Using ADO or DAO matters not because you just want it to execute a SQL Statement.

2. Why do you need to do this? Why a duplicate table? And why not just copy the table instead of going through all of this hassle?

 

[ossuary]

The actual work is being done from an Excel frontend, that's why I'm generating an SQL statement using an ADO connection. The endusers (who are not to be trusted ) will never actually be inside the database itself. So I can't just do it manually.

The intention of this is to store data in one table throughout the month (the "Current" table), and then at the end of the month, copy that table into a new archive table with the month's name, and empty the Current table to use for the next month. I need to keep re-using the Current table over and over again so I can run reports on it without rewriting a bunch of my code in the Excel frontend.

But I can't just use select * into to make the archive tables, because that doesn't bring over the formatting. I need the column widths and formatting to be maintained in the new tables that I'm creating.

 

[SydneyGeek]

Another option would be to leave the table as is, and just append to it. In a query you can have a calculated field that turns every date into the first day of the corresponding month, so 27-May-2011 becomes 1-May-2011. Easy to set-and-forget in the Access database.

Then pull that data back into Excel by filtering on a drop-down list that has the appropriate date list. Look at Part 5 of this tutorial to see what I'm talking about.

 

[ossuary]

I know that I could just store the data in one massive table, flagged with a date or month stamp, but there really is going to be a lot of data in here, and I prefer for multiple reasons to keep the data segregated by month instead, with a smaller, temporary table for the current month's data. To keep access / processing times low, if nothing else.

The more I research this, the more it seems that the "CREATE TABLE AS" syntax type is not available in MS Access for some reason; that format of SQL statement is only available elsewhere, such as mySQL or Oracle. But the "SELECT * INTO" query style doesn't preserve the formatting by default.

You'd think it would be a fairly straightforward command to copy a table, but evidently, Microsoft didn't think that might be necessary...

So I'm still stuck at the first question. How can I create a copy of a table, preserving the formatting of the original table, using Excel and Access?

 

[boblarson]

The more I research this, the more it seems that the "CREATE TABLE AS" syntax type is not available in MS Access for some reason;

You have missed the correct places to research then as Access can create tables using SQL and add fields and all of that.

So I'm still stuck at the first question. How can I create a copy of a table, preserving the formatting of the original table, using Excel and Access?

Instead of using the Create Table method (which if you want to find the syntax, download my free backend autoupdater tool from my Access Tools link on my website (see my signature) and look in the code.

But the simplest would be simply to copy the table is to put this in a standard module and then it will copy the table (you can do your own naming but I just used today's date as you can see) and then it will clear out the original table:

Function CopyAccessTable(strDb As String, strTable As String)
Dim objAcc As Object
 
Set objAcc = CreateObject("Access.Application")
 
objAcc.OpenCurrentDatabase (strDb)
 
objAcc.DoCmd.CopyObject NewName:=strTable & Format(Date, "yyyymmdd"), SourceObjectType:=acTable, SourceObjectName:=strTable
 
objAcc.CurrentDb.Execute "Delete * FROM Records_Disposition"
 
objAcc.Quit
 
Set objAcc = Nothing
 
MsgBox "Done", vbInformation
 
End Function

 

[xenou]

Your archive table can also be one table with a time period stamp -- first or last day of the month). Then you only need to append to it, and you only have one table for your archive which will enhance its value significantly if you ever need to actually do anything with it.

I would also use in the archive table a timestamp (date and time) that is updated when you load it. If you ever forget whether you did it or not (or do it twice), you'll be glad of it.

I should still, in fact, keep a date stamp in your "original" table. This way you can move into the next month without having to have the archiving done - i.e., getting yourself into a bottleneck because of closing processes that need to happen before the next month can start. This may not apply, though, depending on what you are actually doing.

 

[Norie]

Just wondering, do you never require quarterly or yearly reports?

 

[SydneyGeek]

I know that I could just store the data in one massive table, flagged with a date or month stamp, but there really is going to be a lot of data in here, and I prefer for multiple reasons to keep the data segregated by month instead, with a smaller, temporary table for the current month's data. To keep access / processing times low, if nothing else.

How big is "massive"? From what I gather you are pulling data down from a single table, into Excel. How many records are we talking about? The CopyFromRecordSet command can pull 20K records in less than a second; updating a single record from Excel is quick too. If you apply a filter to pull the data that's fast too.
And I have worked with Access tables around the 1 million row mark. Building pivot tables off something like that is slow until the rows have been loaded into the cache because you grab *all* records, but transferring a few thousand records to an Excel sheet isn't a problem.

Denis

 

[ossuary]

We'll be generating in excess of 100,000 rows every month, sometimes as much as 300,000. That's why I don't want all of the data forever in a single table, I want to split them out by month.

And to reference a previous question, there shouldn't be an issue with bottlenecking, as there is a full 2 week gap between when we finish our work with the database for the month and the next month's closing work begins.

All I want is a query that gets the same data as "SELECT * INTO Table2 FROM Table1", but that also brings across the formatting and column widths and whatnot that the original table has been configured with.