Changing name of tables in Access using variables created in VB^

[Gaff]

Hi
My problem is with regards executing a sql string from vb. I created a table in Access and its content will change over time but a copy of what is currently in the table is needed so i created a make table query in access that works fine.
I was going to name the table passed on the current date in VB by passing a date variable to a string i created that holds the string before the INTO part of the SELECT stmt. The problem is the value of the string is in single quotes '' and Access won't let me create the table since it says this is a syntax error. Does anyone know a work around for this.
Following is my basic code:

SQL2 = "SELECT GSInf.UniqueID, GSInf.CMName, GSInf.Type, GSInf.Day, GSInf.Finish INTO " + "'" & today & "'" + " FROM GSInf;"

SQL2 is the string for holding my sqlstmt and today is a date variable i created. I tried changing the type of date variable to string,long,integer but it still gave me a syntax error.
I'm executing the stmt using the connection1.execute where connection is my connection object and it works fine if there is no variable there and i just concatentate to the string but with the introduction of variables the quotes are causing problems. If you have any ideas i would appreciate hearing them.
Thanks

 

[MangroBongacello]

Maybe this might be what you need:

Don't use variable in the sql, just use some temporary name, say MyTable. Then rename the table using TableDef object.

SQL2 = "SELECT GSInf.UniqueID, GSInf.CMName, GSInf.Type, GSInf.Day, GSInf.Finish INTO MyTable FROM GSInf;"


Dim tbl As TableDef
Set tbl = db.TableDefs("MyTable&quot
tbl.Name = today

Hope this helps
Mangro

 

[Gaff]

Is db a database object? I created a db object and set it to the path of my database, which is access 2000 but i get an error saying it is an unrecognised database format:

Dim db As Database
Set db = OpenDatabase("H:\ResearchProject\Visual Basic\TimetableVer2000.mdb&quot

Dim tbl As TableDef
Set tbl = db.TableDefs("MyTable&quot
tbl.name = today

WHat am i doing wrong here, is this working with DAO because i had problems with this before getting the same error, is there any equivalent method in ADO that i could use instead.

 

[WilMead]

Just a peeve but... Change:
SQL2 = "SELECT GSInf.UniqueID, GSInf.CMName, GSInf.Type, GSInf.Day, GSInf.Finish INTO " + "'" & today & "'" + " FROM GSInf;"

To:
SQL2 = "SELECT GSInf.UniqueID, GSInf.CMName, GSInf.Type, GSInf.Day, GSInf.Finish INTO '" & today & "' FROM GSInf;"


Is Today Defined? (Try print SQL2 in immediate window.)
Are the quotes required? Do they hurt when not needed?

When SQL2 gets sent to the Db, the Db doesn't know if the table name was originally there or not. Rewriting it to use a scratch table only proves that the original statement should work.



Wil Mead
wmead@optonline.net