Insert Into Problem

[Andy]

I need to create a query within VBA using the INSERT INTO construct that
combines a value from a variable varz with selected values - something like :-

INSERT INTO tblx ( Field1, Field2, Field3)
SELECT varz, [tbly].Field2, [tbly].Field3
FROM [tbly];

This would hopefully append all rows from table tbly into tblx but with the
contents of variable varz in Field1 in each new row.

The example above doesn't work but it must be close - Can you help?

Thanks.

 

[Rick Brandt]

I need to create a query within VBA using the INSERT INTO construct
that combines a value from a variable varz with selected values -
something like :-

INSERT INTO tblx ( Field1, Field2, Field3)
SELECT varz, [tbly].Field2, [tbly].Field3
FROM [tbly];

This would hopefully append all rows from table tbly into tblx but
with the contents of variable varz in Field1 in each new row.

The example above doesn't work but it must be close - Can you help?

Thanks.

Dim strSQL as String

strSQL = "INSERT INTO tblx ( Field1, Field2, Field3) " & _
"SELECT " & varz & ", [tbly].Field2, [tbly].Field3 " & _
"FROM [tbly];"

CurrentDB,Execute strSQL

Tha above assumes varz is a numeric value. For text...

strSQL = "INSERT INTO tblx ( Field1, Field2, Field3) " & _
"SELECT '" & varz & "', [tbly].Field2, [tbly].Field3 " & _
"FROM [tbly];"

 

[John Vinson]

I need to create a query within VBA using the INSERT INTO construct that
combines a value from a variable varz with selected values - something like :-

INSERT INTO tblx ( Field1, Field2, Field3)
SELECT varz, [tbly].Field2, [tbly].Field3
FROM [tbly];

This would hopefully append all rows from table tbly into tblx but with the
contents of variable varz in Field1 in each new row.

The example above doesn't work but it must be close - Can you help?

Thanks.

SQL has no way to see VBA variable values. Try:

strSQL = "INSERT INTO tblX(Field1, Field2, Field3)" _
& " SELECT " & varZ & " AS Field1, [Field2], [Field3]" _
& " FROM tblY";

Then use either RunSQL or (better, since you can trap errors) a
querydef's Execute method to run the query:

Dim db As DAO.Database
Dim qd As DAO.Querydef
Set db = CurrentDb
Set qd = db.CreateQuerydef("", strSQL) ' nameless query
qd.Execute dbFailOnError

John W. Vinson[MVP]