Run SQL in VBA 2

[micang]

Access 2003,

Hi All,

I am runnig the code below, but nothing happens, no error, nothing. I think the syntax is correct, but I get the feeling I am missing something?

Private Sub Command34_Click()
Dim sql1 As String
sql1 = "SELECT Mm.Dvla_Make, Mm.Dvla_Model_Code, Mm!Dvla_Make & Mm!Dvla_Model_Code AS DVLA_CODE, [Mm]![Dvla_make] AS Make, Mm.Model_Description INTO DVLA_Temp FROM Mm;"
End Sub

It runs when I put the following in the immediate window:

DoCmd.RunSQL "SELECT Mm.Dvla_Make, Mm.Dvla_Model_Code, Mm!Dvla_Make & Mm!Dvla_Model_Code AS DVLA_CODE, [Mm]![Dvla_make] AS Make, Mm.Model_Description INTO DVLA_Temp FROM Mm;"

Any info appreciated.

Michael

 

[Crowley16]

erm, in your command event, where are you actually "running" your sql string?

--------------------
Procrastinate Now!

 

[Remou]

You are missing the run bit;

Private Sub Command34_Click()
Dim sql1 As String
sql1 = "SELECT Mm.Dvla_Make, Mm.Dvla_Model_Code, Mm.Dvla_Make & Mm.Dvla_Model_Code AS DVLA_CODE, [Mm].[Dvla_make] AS Make, Mm.Model_Description INTO DVLA_Temp FROM Mm;"
[b]DoCmd.RunSQL sql1[/b]
End Sub

However, the query does not look like it belongs in a normalized database.

 

[micang]

OK, not sure how to answer this (please bear with me!)

I have a form in a mdb, I have created a command button on the form which I want to do the above (in this case create a new table etc).

The way I have been usually doing this is create the make table query, then a macro, then on the command button run the macro. But I would like to move away from this method and start doing it all in VBA.

So I don't really know how to answer your question!

Thanks for your time.

Michael

 

[Remou]

Which question? Did you see my post?

 

[micang]

Remou, thanks (also, I replied before I saw your post!).

I actaully tried putting "DoCmd.RunSQL sql1", but I put it before the, not after the statement!

Yep, for now the database is not normalized,what I am doing is linking to the mm.txt to create a workable table.

Thank you for your help - star!

Michael

 

[micang]

Grr, both times I replied without seeing your latest post!

 

[Lhuffst]

Hi. This is exactly what I'm trying to do as well. I have a table that I want to extract certain records and put the resultant records in a reports table (already created). Then I run the reports from that table. I tried

ssql = "delete * from tblreportrecords"
db.execute (ssql)

ssql = select fld1, fld2, .... into tblreportrecords from tblcontractinfo where parameters.

db.execute (ssql)

If I use this I get an error that the tblreportrecords already exists.

I switched to:

ssql = "delete * from tblreportrecords"
db.execute (ssql)

ssql = select fld1, fld2, .... into tblreportrecords from tblcontractinfo where parameters.

docmd.runsql (ssql)

and it works fine but I don't want the popup message that says the existing tblreportrecords is about to be deleted, do you want to continue.


Note: When I take the ssql statement from the immediate window and post it in a query, it runs fine every time. The only problem I have is when I try to execute it via code.

SOOOO. If you know what I did wrong on the first version, great or if you know how to get rid of the popup message on the 2nd version that would work just as well.

Thanks Lhuffst

 

[micang]

Hi Lhuffst,

To avoid the warnings you can turn off warnings:

DoCmd.SetWarnings False

then dont forget to turn them back on:
DoCmd.SetWarnings True


So for your second example you do this:

DoCmd.SetWarnings False
ssql = "delete * from tblreportrecords"
db.execute (ssql)

ssql = select fld1, fld2, .... into tblreportrecords from tblcontractinfo where parameters.

docmd.runsql (ssql)
DoCmd.SetWarnings True

Hope this helps.

Michael