Need Help With Macro ("Snapshot" A Linked Table)

[pyrosaxplayer]

Hello,



I am having issues finding the right information for this. Currently, I am trying to accomplish creating a macro that will take a linked table from a database (Already linked in the .accdb file) | Take the whole data in that table (~40,000 records, 3 fields w/ minimal text) | Then proceed to copy it (Back it up, snapshot it etc..) into a new LOCAL table. I also need it to uniquely name it the date it was captured. This will happen every monday.

My task is to take that snapshot and bounce it against the previous weeks snapshot to review the changes (data changes, Rows removed, and Rows added).

I hope that makes sense LOL If you need any more info from me let me know.

[June7]

Could try a SELECT INTO sql action. http://www.w3schools.com/sql/sql_select_into.asp

In VBA, something like:

CurrentDb.Execute "SELECT * INTO ..."

[JoeM]

Here are some links on copying Access tables with VBA:
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx
http://www.geeksengine.com/article/d...ess-table.html

Creating the name is pretty easy too. Here is a variable to calculate the filename based on current date, i.e.

myFileName = Format(Date, "yyyymmdd")

[pyrosaxplayer]

Here are some links on copying Access tables with VBA:
http://msdn.microsoft.com/en-us/libr...ffice.15).aspx
http://www.geeksengine.com/article/d...ess-table.html

Creating the name is pretty easy too. Here is a variable to calculate the filename based on current date, i.e.

myFileName = Format(Date, "yyyymmdd")

Thank you for your reply, I am going to use your suggestion.

But I am having two issues.

1) Where and how do I set that code that would grab and format the date as the name. It is saving it as 00:00:00.

2) It saves it as a link and not a local table.

Sub CopyTable()
    
    Dim myFileName As Date
       
    DoCmd.CopyObject , myFileName, acTable, "cdbfi_inventory_vw"
End Sub

I appreciate all your help everybody I am new to the VBA.

[JoeM]

It saves it as a link and not a local table.

I hadn't thought of that originally, but now it makes sense. Because it is a linked table, if you copy the object, the new object will also be a linked table.
You'll probably have to use the methodology that June suggested, or the first item in the other link I provided (a "Make Table" query).

BTW, regarding the file naming issue. The problem with your code was that:
1. You declared the variable name as a Date (do not do that, choose String)
2. You never set it equal to anything

So, your code should start like this (I am changing the name from "myFileName" to "myTableName" because it makes more sense).

Dim myTableName as String
myTableName=Format(Date,"yyyymmdd")
...

Then you can use myTableName in your create table command.

[pyrosaxplayer]

BTW, regarding the file naming issue. The problem with your code was that:
1. You declared the variable name as a Date (do not do, choose String)
2. You never set it equal to anything

So, your code should start like this (I am changing the name from "myFileName" to "myTableName" because it makes more sense).

Dim myTableName as String
myTableName=Format(Date,"yyyymmdd")
...

Then you can use myTableName in your create table command.

OHHH I see, Thanks again! I will do some more work on it and will post if it is solved or have another issue (CROSSING FINGERS LOL :P )

[pyrosaxplayer]

Alright, everything is working fine EXCEPT! lol

How do I tell VBA to name the table from the pre-defined variable. Obviously, VB is taking the myTableName as a literal string and not executing it like it should.

The Date changed worked on the DoCmd VB line but like we had said it creates it linked

Dim myTableName As String
    
    myTableName = Format(Date, "mmddyyyy")
    
    CurrentProject.Connection.Execute "SELECT * INTO myTableName FROM cdbfi_inventory_vw"

[JoeM]

Everything inside of quotes is treated as literal text. So it is not recognizing your variable as a variable. The variable name cannot be enclosed in the quotes. I think it is easier to follow along if you build the SQL string separately before using it (not necessary, but keeps things looking a little cleaner).

So try this:

Dim myTableName As String
Dim mySQL as String
    
myTableName = Format(Date, "mmddyyyy")

mySQL = "SELECT * INTO " & myTableName & " FROM cdbfi_inventory_vw"
    
CurrentProject.Connection.Execute mySQL

[pyrosaxplayer]

AWESOME! Thank you so much. Goes to show you how much more I have to learn. Issue is all Solved

[JoeM]

Your welcome!