whats wrong with this ??!!

[sconly]

can anybody tell me whats wrong with the SQL statement in the following code ?


Function CreateTable()
Dim TableName As String
Dim strSQL As String

TableName = InputBox("Please type a project name")

DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "subcons", TableName, True
MsgBox "Table " & TableName & " created"

'strSQL = INSERT INTO 'TableName' SELECT Subcons.* FROM Subcons WHERE ((([Subcons]![Tag])="S"));
MsgBox "Data transferred to " & TableName

End Function

i'm trying to copy/append certain records from a table called 'subcons' to a table 'tablename'

 

[ColinEssex]

Line 8 shouldn't have a ' at the front of it

Col

 

[sconly]

removed the ' from line 8, my mistake it wasn't ment to be there.

i know get a compile error

 

[sconly]

i mean i NOW get a compile error.

duh!!

 

[shkrebs]

Hi Sconly...

Well - I'm not an expert - but why do you use a function to do this when you don't need a value return?

Try to put your code into a sub instead.

Søren

 

[sconly]

what do you mean shkrebs ?

 

[sconly]

any chance of a code example ?

 

[Travis]

Sub CreateTable() 
Dim TableName As String 
Dim strSQL As String 

     TableName = InputBox("Please type a project name") 

     DoCmd.TransferDatabase acExport, "Microsoft Access", CurrentDb.Name, acTable, "subcons", TableName, True 
     MsgBox "Table " & TableName & " created" 

     strSQL = "INSERT INTO [" & TableName & "] SELECT Subcons.* FROM Subcons WHERE ((([Subcons]![Tag])="S"));"

     MsgBox "Data transferred to " & TableName 

End Sub

 

[sconly]

thanks travis

but i still get a 'compile error: expected: end of statement' and the 'S' in the "S" is highlighted.

any suggestions

 

[shkrebs]

When you are writing SQL in VBA you shall not end the statement with a ;

The whole statement shall be between " Your-SQL-statement "

Hopes it helps you.

Best regards
Søren

BTW - just like Travis showed you in his example.

 

[sconly]

it did help in a way.
i don't get any errors, but the data doesn't get trasferred.
is it something in my code ??

 

[pdx_man]

Use single quotes around the S. Remove single quotes around tablename. Insert double quote before INSERT

Well, more than all this. Just use this:

strSQL = "INSERT INTO TableName SELECT Subcons.* FROM Subcons WHERE Tag='S' "

Unless Subcons![tag] is referring to the TAG property from a control on a form. ??? What exactly are you trying to do? Is Tag a field name in the table SubCons? Always be careful using reserved words as FieldNames.