Does Access Not Support INSERT ... SET Syntax?

J

JonOfAllTrades

Good afternoon. I've written several INSERT ... SET format queries for
my Access FE/MySQL BE, but Access will not let them through. It reports
"syntax error," even with the dbSQLPassThrough option.
I know my syntax is correct, because it works just fine at the console.
It even works through Access if I create a query, mark it as pass-through,
and run it. But that doesn't help me when I'm working programmatically?
Any ideas? I suppose the problem would be resolved if I stopped using
CurrentDB.Execute and created a Database object to represent MySQL, but I
didn't have any luck with that.
Thank you.
 
M

[MVP] S.Clark

Dunno nothing 'bout no MySQL, but also don't understand why the PTQ catches
the error, either.

Maybe post the entire SQL, and we'll see what else is there.

--
Steve Clark, Access MVP
FMS, Inc.
Call us for all of your Access Development Needs!
1-888-220-6234
[email protected]
www.fmsinc.com/consulting
 
J

JonOfAllTrades

[MVP] S.Clark said:
Dunno nothing 'bout no MySQL, but also don't understand why the PTQ catches
the error, either.

Maybe post the entire SQL, and we'll see what else is there.
Click to expand...

CurrentDb.Execute Query, dbSQLPassThrough does not work, but creating a
stand-along query in design view and making it Pass-Through does work. Of
course, the stand-alone query asks me for a data source every time, so I
can't inflict that on the users.
This is a typical query; the value of my Query string before calling
..Execute:

INSERT INTO Contacts SET FirstName = "Bill", LastName = "Martin", Company =
1163

I know the syntax is valid, at least in MySQL, because it runs fine
when entered into the MySQL console. If Access doesn't support INSERT ...
SET syntax, that's fine, but I'd like to be able to tell it to pass the query
through WITHOUT checking the syntax and reporting an error. Maybe
CurrentDb.Execute Query, dbJustTrustMeOnThisOneOK?
Meanwhile, I've rewritten my code to use INSERT ... VALUES syntax, but
this is less elegant and requires more code. Thus, more opportunities for me
to make a mistake!
Thank you.
 
D

Douglas J. Steele

JonOfAllTrades said:
CurrentDb.Execute Query, dbSQLPassThrough does not work, but creating
a
stand-along query in design view and making it Pass-Through does work. Of
course, the stand-alone query asks me for a data source every time, so I
can't inflict that on the users.
This is a typical query; the value of my Query string before calling
.Execute:

INSERT INTO Contacts SET FirstName = "Bill", LastName = "Martin", Company
=
1163

I know the syntax is valid, at least in MySQL, because it runs fine
when entered into the MySQL console. If Access doesn't support INSERT ...
SET syntax, that's fine, but I'd like to be able to tell it to pass the
query
through WITHOUT checking the syntax and reporting an error. Maybe
CurrentDb.Execute Query, dbJustTrustMeOnThisOneOK?
Click to expand...

You can create a temporary query and define its connection string in code,
thus turning it into a pass-through query. In fact, you don't even need to
use a DSN.

To go against SQL Server, for instance, you can use:

Set qdfCurr = dbCurr.CreateQueryDef("")
qdfCurr.Connect = "ODBC;DRIVER={sql server};" & _
"DATABASE=MyDatabase;SERVER=MyServer" & _
";Trusted_Connection=Yes;"
qdfCurr.SQL = strSQL
qdfCurr.Execute, dbFailOnError

See http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForMySQL for
what to use for MySQL. To connect to a local database (using MyODBC Driver),
for instance, you'd translate what Carl has:

oConn.Open "Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"

to:

qdfCurr.Connect = "ODBC;Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"

so you'd use

strSQL = "INSERT INTO Contacts " & _
"SET FirstName = ""Bill"", " & _
"LastName = ""Martin"", Company = 1163"
Set qdfCurr = dbCurr.CreateQueryDef("")
qdfCurr.Connect = "ODBC;Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"
qdfCurr.SQL = strSQL
qdfCurr.Execute, dbFailOnError
 
J

JonOfAllTrades

Douglas J. Steele said:
You can create a temporary query and define its connection string in code,
thus turning it into a pass-through query. In fact, you don't even need to
use a DSN.

To go against SQL Server, for instance, you can use:

Set qdfCurr = dbCurr.CreateQueryDef("")
qdfCurr.Connect = "ODBC;DRIVER={sql server};" & _
"DATABASE=MyDatabase;SERVER=MyServer" & _
";Trusted_Connection=Yes;"
qdfCurr.SQL = strSQL
qdfCurr.Execute, dbFailOnError

See http://www.carlprothman.net/Default.aspx?tabid=90#ODBCDriverForMySQL for
what to use for MySQL. To connect to a local database (using MyODBC Driver),
for instance, you'd translate what Carl has:

oConn.Open "Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"

to:

qdfCurr.Connect = "ODBC;Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"

so you'd use

strSQL = "INSERT INTO Contacts " & _
"SET FirstName = ""Bill"", " & _
"LastName = ""Martin"", Company = 1163"
Set qdfCurr = dbCurr.CreateQueryDef("")
qdfCurr.Connect = "ODBC;Driver={mySQL};" & _
"Server=MyServerName;" & _
"Option=16834;" & _
"Database=mydb"
qdfCurr.SQL = strSQL
qdfCurr.Execute, dbFailOnError
Click to expand...

Thank you, Doug. That's interesting, and great to know about when I
want to access different databases programmatically. I think in this case
it's a little clearer to just rewrite the query with INSERT ... VALUES
syntax. Unless you think there'd be a speed gain?
I would like to just Open MySQL as a Database, then (I imagine) I could
use MySQLDB.Execute and Access would not police the syntax. I realize this
is an ACCESS forum, but if you see something blindingly obvious I'm missing,
I'd appreciate it. This is my command:

.... = OpenDatabase("", dbDriverNoPrompt, , "ODBC;DSN={MySQL}")

MySQL is the name of my server-level DSN. When this command executes,
I'm prompted with the ODBC dialog. I can choose the correct DSN, and it
seems to work OK after that, but I don't understand why I'm getting a prompt.
I also tried this:

.... = OpenDatabase("MySQL", dbDriverNoPrompt)

And various variations thereof, including "DSN=MySQL",
"ODBC;DSN=MySQL", each of these with curly braces, etc. The documentation on
OpenDatabase is pretty vague, I guess I need to research ODBC a bit more.
 
D

Douglas J. Steele

JonOfAllTrades said:
Thank you, Doug. That's interesting, and great to know about when I
want to access different databases programmatically. I think in this case
it's a little clearer to just rewrite the query with INSERT ... VALUES
syntax. Unless you think there'd be a speed gain?
I would like to just Open MySQL as a Database, then (I imagine) I
could
use MySQLDB.Execute and Access would not police the syntax. I realize
this
is an ACCESS forum, but if you see something blindingly obvious I'm
missing,
I'd appreciate it. This is my command:

... = OpenDatabase("", dbDriverNoPrompt, , "ODBC;DSN={MySQL}")

MySQL is the name of my server-level DSN. When this command executes,
I'm prompted with the ODBC dialog. I can choose the correct DSN, and it
seems to work OK after that, but I don't understand why I'm getting a
prompt.
I also tried this:

... = OpenDatabase("MySQL", dbDriverNoPrompt)

And various variations thereof, including "DSN=MySQL",
"ODBC;DSN=MySQL", each of these with curly braces, etc. The documentation
on
OpenDatabase is pretty vague, I guess I need to research ODBC a bit more.
Click to expand...

If I recall correctly, you must specify values for all of the parameters in
the OpenDatabase method. Yes, I know the Help file says that they're
optional, but all that means is that you don't need to provide them at all.
If you skip one, I believe it stops looking for any remaining parameters.
 
J

JonOfAllTrades

Douglas J. Steele said:
If I recall correctly, you must specify values for all of the parameters in
the OpenDatabase method. Yes, I know the Help file says that they're
optional, but all that means is that you don't need to provide them at all.
If you skip one, I believe it stops looking for any remaining parameters.
Click to expand...

Huzzah! Praise be to Doug! I had to put the whole connection string
in and bypass the DSN, but I got it working, and you are absolutely right: it
requires the "optional" parameter of "ReadOnly," and possibly others.
This is what finally worked:

Set MySQL = OpenDatabase("MySQL", dbDriverNoPrompt, False,
"ODBC;DRIVER={MySQL ODBC 3.51
Driver};DSN={MySQL};SERVER=server;DATABASE=tms;USER=root;PASSWORD=mypassword")

THIS did not work:

.... OpenDatabase("MySQL", dbDriverNoPrompt, , "ODBC;DRIVER=...

I hope to see a significant performance improvement, and hopefully
Access will also stop vetting my queries. Thank you!!!
 
Top