SQL in VBA

[Thales750]

Good afternoon Everyone,
Well it's easy to see why people don't break out of the box and try new stuff.
You never really get it right the first time, and then you need to force yourself to work through it.
I've been building databases in Access since 1995 and until now I have never needed to write a single line of SQL, and last week marked the first time ever to make a loop since my days playing around with DOS BASIC. . .
when dinosours roamed.

Could someone please tell me why this SQL statement copied directly from a saved query doesn't work in VBA

Private Sub Command2_Click()
 
DoCmd.RunSQL "INSERT INTO temp ( UserLoginName, LocconID )" & _
"SELECT tblUserLogin.UserLoginName, tblUserLogin.LocconID" & _
"FROM tblUserLogin;"
 
End Sub

It works fine in a saved query.
From the module, It returns this error
Run-time error '3075':
Syntax error (missing operator) in query expression
'tblUserLogin.LocconIDFROM tblUserLogin'.
Thanks all,
Joe

 

[MSAccessRookie]

Good afternoon Everyone,
Well it's easy to see why people don't break out of the box and try new stuff.
You never really get it right the first time, and then you need to force yourself to work through it.
I've been building databases in Access since 1995 and until now I have never needed to write a single line of SQL, and last week marked the first time ever to make a loop since my dayes playing around with DOS BASIC. . .
when dinosours roamed.

Could someone please tell me why this SQL statement copied directly from a saved query doesn't work in VBA

Private Sub Command2_Click()
 
DoCmd.RunSQL "INSERT INTO temp ( UserLoginName, LocconID )" & _
"SELECT tblUserLogin.UserLoginName, tblUserLogin.LocconID" & _
"FROM tblUserLogin;"
 
End Sub

It works fine in a saved query.
From the module, It returns this error
Run-time error '3075':
Syntax error (missing operator) in query expression
'tblUserLogin.LocconIDFROM tblUserLogin'.
Thanks all,
Joe

One of the easiest errors to make when moving from SQL Query Format to VB SQL Format is forgetting to add a space (or a line break). If you take your query and expand it it looks like this:

INSERT INTO temp ( UserLoginName, LocconID [COLOR=red][B])SELECT[/B][/COLOR] tblUserLogin.UserLoginName, [COLOR=red][B]tblUserLogin.LocconIDFROM[/B][/COLOR] tblUserLogin;"

Adding spaces or line breaks in the locations indicated above should be what you need. It probably works in the saved query, because of the line breaks.

 

[Thales750]

Thank you so much, for making my miserable existence a little brighter.

Wow that was great, a simple fix and all is well.

Joe

 

[MSAccessRookie]

Thank you so much, for making my miserable existence a little brighter.

Wow that was great, a simple fix and all is well.

Joe

Not a problem. I used to do that all of the time. Now it is the first thing I look for if I have an error running SQL using VB.

 

[raskew]

Hi -

If you're going to be doing very much of this, you might take a look at Allen Browne's solution at http://allenbrowne.com/ser-71.html

It works really well.

Bob

 

[boblarson]

This is kind of cool too:

http://www.sqlinform.com/

 

[Banana]

Just to point out one more thing-

If the SQL in VBA are something like:

"INSERT INTO foo (bar, baz) VALUES (" & Me.MyTextbox1 & ", " & Me.MyTextBox2 & ");"

Then IMHO, 1) this is unnecessary work, 2) this will add bloat and hurt performance.

Better to write a parameter query and execute it:

With CurrentDb.QueryDefs("MyParameterQuery")
   .Parameters("p1") = Me.MyTextBox1
   .Parameters("p2") = Me.MyTextBox2
   .Execute
End With

with the parameter query's SQL being:

PARAMETERS p1 TEXT(255), p2 TEXT(255);
INSERT INTO foo (bar, baz) VALUES ([p1], [p2]);

The only reason for having SQL in VBA is when you need to dynamically select various numbers of columns or don't know which table you want to use, something that parameter queries aren't well-suited for, but such cases are quite few and far between, so for most parts, parameter queries should be used instead. Better yet, no messing with formatting; just load the parameter and run it.

 

[Thales750]

Hey Banana,

Alright I tried pasting that code.

With the following modifications:

Private Sub Command7_Click()
 
With CurrentDb.QueryDefs("qryEventLogParameter")
 
    .Parameters(“p1”) = Me.Text1
 
    .Parameters("p2") = Me.Text3
 
    .Parameters("p3") = Me.Text5
 
    .Parameters("p4") = Me.Text8
 
    .Parameters("p5") = Me.Text10
 
 
   .Execute
 
End With
 
End Sub

I’m thinking I must need to define something or another.

The code stops at the first parameter

Here's the SQL:

PARAMETERS p1 Text ( 255 ), p2 IEEEDouble, p3 Text ( 255 ), p4 IEEEDouble, p5 Text ( 255 );
INSERT INTO tblUserActivityLog ( Function, DateIn, UserID, TableIdentifier, Function, Notes )
SELECT [p1] AS Expr1, Now() AS Expr2, [p2] AS Expr3, [p3] AS Expr4, [p4] AS Expr5, [p5] AS Expr6;






Thanks,

Joe

 

[MSAccessRookie]

I don't know if it is relevant, the Quotes in the first parameter are not the same as the ones in the rest of the parameters. “ vs. ". Since I have had similar issues in the past, I wanted to point that out.

Afterthought:

All of your fields are named Text, but some of the parameters appear to be non-text. If the field names are just that, then as long as the field types are correct, I cannot think of anything else at this time.

It looks like Gemma is thinking similar thoughts. Check out all of the parameters at both ends and make sure that all of them are what they are supposed to be.

 

[gemma-the-husky]

Banana is obviously your expert, but it might be a data mismatch

ie you are inserting a date for parameter2, but parameter 2 is set up as a double

 

[Banana]

MSAR and Gemma already identified possible issues. I, too have been bit by that weird “ marks which isn't same as " marks.

Also, so you know, you can declare parameters to be date type as well. While dates are basically double, I think consistency in how they are declared will help us avoid any weirdness that may arise from implicit conversions.

If you go to VBA help file for "Comparison of Data Types" and look at Jet's column, you will see what keyword Jet expects for its datatypes compared to VBA, ADO, DAO, and Access UI.

HTH.

 

[Thales750]

Banana is obviously your expert, but it might be a data mismatch

ie you are inserting a date for parameter2, but parameter 2 is set up as a double

Thank you, gemma-the-husky

And no I didn't consider anyone to be "my expert", I was just trying to be polite.

Thanks also to MSAccessRookie

I'll

 

[Thales750]

Banana is obviously your expert, but it might be a data mismatch

ie you are inserting a date for parameter2, but parameter 2 is set up as a double

Thank you, gemma-the-husky

And no I didn't consider anyone to be "my expert", I was just trying to be polite.

Thanks also to MSAccessRookie

I'll make a simple query and try it with only text.

 

[Thales750]

This simple little operation got past parameter.
but failed at execute.

Private Sub Command14_Click()
 
 
With CurrentDb.QueryDefs("Query1")
    .Parameters("p1") = Me.Text1
 
   .Execute
 
End With
 
End Sub

SQL still in the query def not in module.

PARAMETERS p1 Text ( 255 );
SELECT tblContacts.FirstName
FROM tblContacts
WHERE (((tblContacts.FirstName)=[p1]));

 

[MSAccessRookie]

This simple little operation got past parameter.
but failed at execute.


Private Sub Command14_Click()

With CurrentDb.QueryDefs("Query1")
    .Parameters("p1") = Me.Text1
 
   .Execute
 
End With
 
End Sub

SQL still in the query def not in module.

PARAMETERS p1 Text ( 255 );
SELECT tblContacts.FirstName
FROM tblContacts
WHERE (((tblContacts.FirstName)=[p1]));

You state that p1 is defined as Text ( 255 ). Is the value contained in Me.Text1 consistant with that? A Memo or Non-Text Field might not be.

 

[Banana]

I'll whip a sample as soon as I'm finished running a long-runining process locking up my VM...

 

[Banana]

After testing this and verifying that the syntaxes were correct, I realized why you were getting an error in your simple query.

You're doing a SELECT query. We can't do a .Execute upon a SELECT query; only action queries such as UPDATE or INSERT INTO (append) or DELETE.

If you want to use SELECT, you would do this instead:

Dim r As DAO.Recordset
With CurrentDb.QueryDefs("MyQuery")
   .Parameters("p1") = "foo"
   .Parameters("p2") = 2
   Set r = .OpenRecordset (dbOpenDynaset)
End With

What are you going to do with the parameter query? If it's to be bound to a form, then we'll need to know more details.

With your earlier query, I'd double check the data types- they have to be all consistent in three possible: in the table design view, in query's parameter dialog, and in VBA.

 

[gemma-the-husky]

what i meant was that Banana has demonstrated in many threads his intimate understanding of the usage of parameter queries, and was likely to be able to idenitfy any real issues.

I had just observed the apparent discrepancy between the data type. and the usage.

 

[Thales750]

what i meant was that Banana has demonstrated in many threads his intimate understanding of the usage of parameter queries, and was likely to be able to idenitfy any real issues.

I had just observed the apparent discrepancy between the data type. and the usage.

Thank you for helping me. this is a wonderful gift we are all given. I for one appreciate all contributions and opinions. Rarely do you find so many people willing to give as freely as here.

So it sounds to me like I fixed one problem and introduced another. LOL.

I’ll give it a try to make sure all my data types match up and that I execute an action query.

I’ll report back after my next cup of coffee.

Here is a record. hehe, pun intended.


tblUserActivityLogIDUserIDDateInDateOutPrmaryIDTableIdentifierFunctionNotes7644Joe.Sanders3/12/2009 9:30:42 AM


Thanks guys,
Joe