Make Table Query (1 Viewer)

[123dstreet]

Hi all!

I have managed to create a Make Table Query that sends the selected table information to a new database. Is there anyway to make this an automatic function, and not delete the previous table, make a new table and automatically rename that table with code? Any help on this would be great! Thanks

 

[John Big Booty]

Do you want to just keep creating tables with, say, and incrementing name?

 

[John Big Booty]

If the answer to the above is yes the following should do the trick;

    Dim strSQL As String
    Dim intTableCnt As Integer
    
    [COLOR="SeaGreen"]'Count Current Tables with Table Name that starts with YourTableName[/COLOR]
    intTableCnt = DCount("Name", "MSysObjects", "Name like '[COLOR="Red"]YourTableName[/COLOR]*'")
    
    [COLOR="SeaGreen"]'Create "make table" SQL string[/COLOR]
    strSQL = "SELECT [COLOR="Red"]YourTableName[/COLOR].* INTO [COLOR="Red"]YourTableName[/COLOR]" & intTableCnt + 1 & " FROM [COLOR="Red"]YourTableName[/COLOR];"
        
    [COLOR="SeaGreen"]'Run make table SQL string[/COLOR]
    DoCmd.RunSQL strSQL

Change only the Red highlighted sections to reflect the reality of your DB.

 

[123dstreet]

Thank you, I will give that a shot!

The basic goal of this is to take a 'snapshot' of this table today, and then take subsequent 'snapshots' of this table everyday. I want to be able to compare the most recent table with the previous days table. It is basically meant to keep track of changes to this one table in the DB.

 

[John Big Booty]

In that case, so long as you only run the query once a day perhaps the following would be more appropriate;

    Dim strSQL As String
    Dim strDate As String
    
    [COLOR="SeaGreen"]'Create date string[/COLOR]
    strDate = DatePart("yyyy", Date) & DatePart("m", Date) & DatePart("d", Date)
       
    [COLOR="SeaGreen"]'Create "make table" SQL string[/COLOR]
    strSQL = "SELECT [COLOR="Red"]YourTableName[/COLOR].* INTO [COLOR="Red"]YourTableName[/COLOR]" & strDate & "  FROM [COLOR="Red"]YourTableName[/COLOR];"
        
    [COLOR="SeaGreen"]'Run make table SQL string[/COLOR]
    DoCmd.RunSQL strSQL

 

[123dstreet]

I have noticed that every time I run the query and create the new table, the data in this new table overwrites the data in each previous table. Is there any way to stop this from happening?

 

[John Big Booty]

Are you sure Which code are you using?

I've just been playing with the my first code and can't replicate the behaviour you describe

 

[John Big Booty]

...are you able to post a copy of your DB?

 

[123dstreet]

Unfortunately I can't release this DB. I forgot to mention also, When I run the query, I would like the new table to be created in a different database.

I tried using the 2nd code you posted, but it would not work.. I received an error message saying, "Query input must contain at least one table or query".

I think I am missing a key element to this.

 

[John Big Booty]

Both the codes worked as expected in my Sandbox DB, so without being able to see what you have done and what is happening, I'm afraid you'll have to nut this out on your own.

As for making your table in another DB, I know it's possible, I just have to learn how it's done, then I'll post back

 

[John Big Booty]

The following should create your table in another DB;

    Dim strSQL As String
    Dim strDate As String
    
    [COLOR="SeaGreen"]'Create date string[/COLOR]
    strDate = DatePart("yyyy", Date) & DatePart("m", Date) & DatePart("d", Date)
       
    [COLOR="SeaGreen"]'Create "make table" SQL string[/COLOR]
    strSQL = "SELECT [COLOR="Red"]YourTableName[/COLOR].* INTO [COLOR="Red"]YourTableName[/COLOR]" & strDate & "  IN '[COLOR="Red"]C:\YourPath\YourOtherDBName.mdb[/COLOR]' FROM [COLOR="Red"]YourTableName[/COLOR];"
        
    [COLOR="SeaGreen"]'Run make table SQL string[/COLOR]
    DoCmd.RunSQL strSQL

Once again change only the Red highlighted sections to reflect the reality of your DB.