SQL in VBA Help Please (1 Viewer)

[Ripley]

Hi all. Ive searched the forums but theres nothing that adheres to excatly what i need.

What i want to do is create an SQL expression in a VBA enviroment, to create a table, and insert data from a query into that table.

I do not wish to use a create table query, this must be done in vba!

For example:

Dim strSQL As String
strSQL = "CREATE TABLE etc...."

DoCmd.RunSQL(strSQL)

I just need someone to write me some generic code if they could please.

Thanks in advance!

 

[Rich]

Have you looked in the Help file for create table?

 

[Ripley]

yeah, no luck there...

 

[boblarson]

I do not wish to use a create table query, this must be done in vba!

For example:

Dim strSQL As String
strSQL = "CREATE TABLE etc...."

DoCmd.RunSQL(strSQL)

Um, you already have the tools at your disposal. You can create your make table query in the QBE grid and then change it to SQL view to copy the syntax and then you can paste it into your
strSQL = ""
spot. In fact, that's what I do all of the time.

There are just a couple of things to remember when doing this.
1. When you paste, it will be on multiple lines and give you the red syntax error until you add line extenders (an underscore) and quotation marks.

2. You will also need to add a space after the last part of each line (except the last line) and the quotation marks. So, for example, here's something from the QBE grid:


SELECT TblMainRA.RADate, TblMainRA.RANum, TblMainRA.ClaimNum, TblMainRA.dealer, TblMainRA.customer, TblMainRA.StatusID, TblMainRA.IncidentID
FROM TblMainRA;


And to turn it into something that will work is to do this:

strSQL = "SELECT TblMainRA.RADate, TblMainRA.RANum, TblMainRA.ClaimNum, TblMainRA.dealer, TblMainRA.customer, TblMainRA.StatusID, TblMainRA.IncidentID " & _
"FROM TblMainRA;"


3. Any references to variables or controls need to be broken out of the string by using quotes before, an ampersand, the item referenced, an ampersand, and then quotes again to start the string back up.

So for this:

SELECT TblMainRA.RADate, TblMainRA.RANum, TblMainRA.ClaimNum, TblMainRA.dealer, TblMainRA.customer, TblMainRA.StatusID, TblMainRA.IncidentID
FROM TblMainRA
WHERE (((TblMainRA.RANum)=[Forms]![FrmClaim]![txtClaimNum]));

I would use it in VBA like this:

strSQL = "SELECT TblMainRA.RADate, TblMainRA.RANum, TblMainRA.ClaimNum, TblMainRA.dealer, TblMainRA.customer, TblMainRA.StatusID, TblMainRA.IncidentID " & _
"FROM TblMainRA " & _
"WHERE (((TblMainRA.RANum)=" & [Forms]![FrmClaim]![txtClaimNum] & "));"

And if the text box is referencing an actual text object you would include single quotes:

"WHERE (((TblMainRA.RANum)='" & [Forms]![FrmClaim]![txtClaimNum] & "'));"

 

[Ripley]

dont know why I didnt think of that before thanks!