DSN-less ODBC questions

[Eric E]

Two questions regarding ODBC.

1) I am connecting to a MySQL database using the following code:

Function LoginToMySQL(sUsername As String, sPW As String) As Boolean
Dim dbMySQL As Database
Dim cnMySQL As DAO.Connection
Dim sDSN As String
sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& " DATABASE=lims;" _
& "UID=" & sUsername & ";PWD=" & sPW _
& ";OPTION=16386"
Set dbMySQL = OpenDatabase("", dbDriverNoPrompt, , sDSN)
End Function

When I get to the OpenDatabase call, I am prompted to chose an existing
DSN. I would like to be able to make the connection DSN-less. Is this
possible? Is it driver specific? Any code changes anyone can recommend?

2) When inserting a record via ODBC & DAO into a table with an
autonumber primary key, I would like to get the value of the new primary
key after insert. Using ADO and Jet, it's possible to use "SELECT
@@IDENTITY" to retrieve this value. How can I get the autonumber of the
inserted record using DAO and ODBC?

Many thanks for any ideas or answers to either question.

Cheers,

Eric

[rkc]

"Eric E" <e@a> wrote in message news:po********************@speakeasy.net...

Two questions regarding ODBC.

1) I am connecting to a MySQL database using the following code:

Function LoginToMySQL(sUsername As String, sPW As String) As Boolean
Dim dbMySQL As Database
Dim cnMySQL As DAO.Connection
Dim sDSN As String
sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& " DATABASE=lims;" _
& "UID=" & sUsername & ";PWD=" & sPW _
& ";OPTION=16386"
Set dbMySQL = OpenDatabase("", dbDriverNoPrompt, , sDSN)
End Function

When I get to the OpenDatabase call, I am prompted to chose an existing
DSN. I would like to be able to make the connection DSN-less. Is this
possible? Is it driver specific? Any code changes anyone can recommend?

2) When inserting a record via ODBC & DAO into a table with an
autonumber primary key, I would like to get the value of the new primary
key after insert. Using ADO and Jet, it's possible to use "SELECT
@@IDENTITY" to retrieve this value. How can I get the autonumber of the
inserted record using DAO and ODBC?

You're being prompted because you're not supplying a database name.

If you enter a new record via DAO .AddNew, .Update you can move to
the new record using .Bookmark = .LastModified. If not, I believe you
have to issue a Select query to retrieve the record you just inserted.

[Eric Ellsworth]

Thanks for the quick answers, rkc. I have to admit I'm confused:

Doesn't "DATABASE=lims" specify the database?
Do I need quotes around it or some such?

As for the adding a new record, I will try that. Thanks.

EE
rkc wrote:

"Eric E" <e@a> wrote in message news:po********************@speakeasy.net...

Two questions regarding ODBC.

1) I am connecting to a MySQL database using the following code:

Function LoginToMySQL(sUsername As String, sPW As String) As Boolean
Dim dbMySQL As Database
Dim cnMySQL As DAO.Connection
Dim sDSN As String
sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& " DATABASE=lims;" _
& "UID=" & sUsername & ";PWD=" & sPW _
& ";OPTION=16386"
Set dbMySQL = OpenDatabase("", dbDriverNoPrompt, , sDSN)
End Function

When I get to the OpenDatabase call, I am prompted to chose an existing
DSN. I would like to be able to make the connection DSN-less. Is this
possible? Is it driver specific? Any code changes anyone can recommend?

2) When inserting a record via ODBC & DAO into a table with an
autonumber primary key, I would like to get the value of the new primary
key after insert. Using ADO and Jet, it's possible to use "SELECT
@@IDENTITY" to retrieve this value. How can I get the autonumber of the
inserted record using DAO and ODBC?

You're being prompted because you're not supplying a database name.

If you enter a new record via DAO .AddNew, .Update you can move to
the new record using .Bookmark = .LastModified. If not, I believe you
have to issue a Select query to retrieve the record you just inserted.

[rkc]

"Eric Ellsworth" <ez@e> wrote in message
news:Pq********************@speakeasy.net...

Thanks for the quick answers, rkc. I have to admit I'm confused:

Doesn't "DATABASE=lims" specify the database?
Do I need quotes around it or some such?

You would think that wouldn't you?

Try taking the database name out of the sDSN string and adding it to the
first argument of OpenDatabase method.

[Bri]

Eric,

1) I connect to SQL Server via Linked Tables. Once the link is created
you don't need to relink it everytime you open the database. I use the
following function to create the link:

Function Link2ODBC(stTableName As Variant)
Dim tbl As DAO.TableDef, stConnect As String
stConnect = "ODBC;DRIVER=SQL Server; SERVER=myServer; UID=myUID; _
PWD=myPWD;DATABASE=myDatabase"
Set tbl = db.CreateTableDef(stTableName, dbAttachSavePWD, _
stTableName, stConnect)
db.TableDefs.Append tbl
End Function

2) In Jet, the AutoNumber is assigned during the Update and the
recordset continues to point at the added record. In SQL Server the
Identity is assigned when the record is initiated, so it is available
before the Update (which drops the pointer to the added record). I do it
with a DAO Dynaset recordset, but other recordset types may work similarly.

Jet
===
rs.AddNew
rs!Data = "something"
rs.Update
lPK=rs!PK

SQL Server
==========
rs.AddNew
rs!Data = "something"
lPK=rs!PK
rs.Update

Hope that helps.
---
Bri

Eric E wrote:

Two questions regarding ODBC.

1) I am connecting to a MySQL database using the following code:

Function LoginToMySQL(sUsername As String, sPW As String) As Boolean
Dim dbMySQL As Database
Dim cnMySQL As DAO.Connection
Dim sDSN As String
sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& " DATABASE=lims;" _
& "UID=" & sUsername & ";PWD=" & sPW _
& ";OPTION=16386"
Set dbMySQL = OpenDatabase("", dbDriverNoPrompt, , sDSN)
End Function

When I get to the OpenDatabase call, I am prompted to chose an existing
DSN. I would like to be able to make the connection DSN-less. Is this
possible? Is it driver specific? Any code changes anyone can recommend?

2) When inserting a record via ODBC & DAO into a table with an
autonumber primary key, I would like to get the value of the new primary
key after insert. Using ADO and Jet, it's possible to use "SELECT
@@IDENTITY" to retrieve this value. How can I get the autonumber of the
inserted record using DAO and ODBC?

Many thanks for any ideas or answers to either question.

Cheers,

Eric

[Eric Ellsworth]

Hi Bri,
Thanks, I've been evaluating that method as well. There are two
drawbacks at present. One is that I want to pass the username and pw to
the data source at login, and I'm concerned that re-linking tables at
every login will incur a substantial performance penalty (it sure was
linking to an MDB on a file server).
The other is that because of the way permissions work in MySQL and
MyODBC, a user who does not have permission to all columns of a table
gets an error. I'd like to avoid this situation.
Nonetheless, this is something I'll continue to look at. Thanks for
the advice.

Bri wrote:

Eric,

1) I connect to SQL Server via Linked Tables. Once the link is created
you don't need to relink it everytime you open the database. I use the
following function to create the link:

Function Link2ODBC(stTableName As Variant)
Dim tbl As DAO.TableDef, stConnect As String
stConnect = "ODBC;DRIVER=SQL Server; SERVER=myServer; UID=myUID; _
PWD=myPWD;DATABASE=myDatabase"
Set tbl = db.CreateTableDef(stTableName, dbAttachSavePWD, _
stTableName, stConnect)
db.TableDefs.Append tbl
End Function

2) In Jet, the AutoNumber is assigned during the Update and the
recordset continues to point at the added record. In SQL Server the
Identity is assigned when the record is initiated, so it is available
before the Update (which drops the pointer to the added record). I do it
with a DAO Dynaset recordset, but other recordset types may work similarly.

Jet
===
rs.AddNew
rs!Data = "something"
rs.Update
lPK=rs!PK

SQL Server
==========
rs.AddNew
rs!Data = "something"
lPK=rs!PK
rs.Update

Hope that helps.
---
Bri

Eric E wrote:

Two questions regarding ODBC.

1) I am connecting to a MySQL database using the following code:

Function LoginToMySQL(sUsername As String, sPW As String) As Boolean
Dim dbMySQL As Database
Dim cnMySQL As DAO.Connection
Dim sDSN As String
sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=127.0.0.1;" _
& " DATABASE=lims;" _
& "UID=" & sUsername & ";PWD=" & sPW _
& ";OPTION=16386"
Set dbMySQL = OpenDatabase("", dbDriverNoPrompt, , sDSN)
End Function

When I get to the OpenDatabase call, I am prompted to chose an
existing DSN. I would like to be able to make the connection
DSN-less. Is this possible? Is it driver specific? Any code changes
anyone can recommend?

2) When inserting a record via ODBC & DAO into a table with an
autonumber primary key, I would like to get the value of the new
primary key after insert. Using ADO and Jet, it's possible to use
"SELECT @@IDENTITY" to retrieve this value. How can I get the
autonumber of the inserted record using DAO and ODBC?

Many thanks for any ideas or answers to either question.

Cheers,

Eric

[Eric Ellsworth]

Hi rkc,
When I run this code:

Dim cnMySQL As DAO.Connection
Dim sDSN As String
' & "DATABASE=lims;"
sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& "PORT=3306;" _
& "UID=" & sUsername & ";PWD=" & sPW &
";OPTION=16386"

Dim wrkODBC As Workspace
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set cnMySQL = wrkODBC.OpenConnection("lims", dbDriverNoPrompt, , sDSN)

I get:
Runtime error 3146
ODBC -- call failed

And from
?DAO.Errors(0)
IM002: [Microsoft][ODBC Driver Manager] Data source name not found and
no default driver specified

Any ideas?

I am able to open a connection by specifying a file DSN, which is a
decent enough workaround.
When I do that I am getting the dreaded Runtime Error 3027
Cannot uopdate. Database or object is read only,
despite using the following OpenRecordset call:

sPatientSQL = "SELECT * FROM patients WHERE patientID=11;"
Set rsPatients = cnMySQL.OpenRecordset(sPatientSQL, dbOpenDynamic,
0, dbOptimistic)

Any good ideas on this one?

As always, many thanks.

Eric

rkc wrote:

"Eric Ellsworth" <ez@e> wrote in message
news:Pq********************@speakeasy.net...

Thanks for the quick answers, rkc. I have to admit I'm confused:

Doesn't "DATABASE=lims" specify the database?
Do I need quotes around it or some such?

You would think that wouldn't you?

Try taking the database name out of the sDSN string and adding it to the
first argument of OpenDatabase method.

[rkc]

"Eric Ellsworth" <ez@e> wrote in message
news:lp********************@speakeasy.net...

The following format works for me and allows opening a recordset
using the dao.connection.openrecordset method. But, like you I can't
figure out how to return an updateable recordset. Not even with
a timestamp field added to the MySQL table.

Dim cnMySQL As DAO.Connection
Dim sDSN As String

sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& "PORT=3306;" _
& "UID=" & sUsername & ";PWD=" & sPW _
& "DATABASE=lims;" _
& "OPTION=16386"

Dim wrkODBC As Workspace

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)

Set cnMySQL = wrkODBC.OpenConnection _
(NewODBCWorkspace, dbDriverNoPrompt, False, sDSN)

[Douglas J. Steele]

Is there a unique index defined for the table in MySQL? That's a requirement
of ODBC.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"rkc" <rk*@yabba.dabba.do.rochester.rr.bomb> wrote in message
news:dq*****************@twister.nyroc.rr.com...

"Eric Ellsworth" <ez@e> wrote in message
news:lp********************@speakeasy.net...

The following format works for me and allows opening a recordset
using the dao.connection.openrecordset method. But, like you I can't
figure out how to return an updateable recordset. Not even with
a timestamp field added to the MySQL table.

Dim cnMySQL As DAO.Connection
Dim sDSN As String

sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& "PORT=3306;" _
& "UID=" & sUsername & ";PWD=" & sPW _
& "DATABASE=lims;" _
& "OPTION=16386"

Dim wrkODBC As Workspace

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)

Set cnMySQL = wrkODBC.OpenConnection _
(NewODBCWorkspace, dbDriverNoPrompt, False, sDSN)

[Eric Ellsworth]

Douglas J. Steele wrote:

Is there a unique index defined for the table in MySQL? That's a requirement
of ODBC.

Yes. the primary key has a unique index on it called "PRIMARY". Thanks
for the tip, though.

EE

[Eric Ellsworth]

Thanks, rkc. What do you do as a workaround for not being able to open
an updatable recordset?

EE

rkc wrote:

"Eric Ellsworth" <ez@e> wrote in message
news:lp********************@speakeasy.net...

The following format works for me and allows opening a recordset
using the dao.connection.openrecordset method. But, like you I can't
figure out how to return an updateable recordset. Not even with
a timestamp field added to the MySQL table.
Dim cnMySQL As DAO.Connection
Dim sDSN As String

sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& "PORT=3306;" _
& "UID=" & sUsername & ";PWD=" & sPW _
& "DATABASE=lims;" _
& "OPTION=16386"

Dim wrkODBC As Workspace

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)

Set cnMySQL = wrkODBC.OpenConnection _
(NewODBCWorkspace, dbDriverNoPrompt, False, sDSN)

[R Baumann]

I'm not exactly positive about this, but I THINK your OPTION is wrong.
According to
http://dev.mysql.com/doc/connector/o...tml#Connection your option
should be 1 + 2+ 16384 = 16387. I recall problems a couple of years ago if
option 1 & 2 were not selected. Also make double sure that you have the
right version of MDAC installed. Sorry, wish I could offer more, but it's
been years since I've run MySQL.

HOWEVER, just as a FYI, and Access Front End with a MySQL backend was a
GREAT combo. Also, you might want to look at this website
http://www.hiden.org/myaccess/

HTH
Ruben

"Eric Ellsworth" <ez@e> wrote in message
news:lp********************@speakeasy.net...

Hi rkc,
When I run this code:

Dim cnMySQL As DAO.Connection
Dim sDSN As String
' & "DATABASE=lims;"
sDSN = "ODBC;DRIVER={MySQL ODBC 3.51 Driver};" _
& "SERVER=localhost;" _
& "PORT=3306;" _
& "UID=" & sUsername & ";PWD=" & sPW &
";OPTION=16386"

Dim wrkODBC As Workspace
Set wrkODBC = CreateWorkspace("NewODBCWorkspace", _
"admin", "", dbUseODBC)
Set cnMySQL = wrkODBC.OpenConnection("lims", dbDriverNoPrompt, , sDSN)
I get:
Runtime error 3146
ODBC -- call failed

And from
?DAO.Errors(0)
IM002: [Microsoft][ODBC Driver Manager] Data source name not found and
no default driver specified

Any ideas?

I am able to open a connection by specifying a file DSN, which is a
decent enough workaround.
When I do that I am getting the dreaded Runtime Error 3027
Cannot uopdate. Database or object is read only,
despite using the following OpenRecordset call:

sPatientSQL = "SELECT * FROM patients WHERE patientID=11;"
Set rsPatients = cnMySQL.OpenRecordset(sPatientSQL, dbOpenDynamic,
0, dbOptimistic)

Any good ideas on this one?

As always, many thanks.

Eric

rkc wrote:

"Eric Ellsworth" <ez@e> wrote in message
news:Pq********************@speakeasy.net...

Thanks for the quick answers, rkc. I have to admit I'm confused:

Doesn't "DATABASE=lims" specify the database?
Do I need quotes around it or some such?

You would think that wouldn't you?

Try taking the database name out of the sDSN string and adding it to the
first argument of OpenDatabase method.

[Bri]

Eric,

When I create the link I leave it in place with the authentication
stored with the link (see the dbAttachSavePWD option). Is there a reason
that you think that this link needs to be re-established (or
re-authenticated) each time the user opens the application? You should
have enough control over what the user can do with the linked table
using Access User-Group security. So, just create the link, apply
user-group permissions to the link and you're done. No need to destroy
and recreate the link.

Or is each Access UserID being echoed as a mySQL UserID? Sounds like a
maintenance nightmare.

---
Bri

Eric Ellsworth wrote:

Hi Bri,
Thanks, I've been evaluating that method as well. There are two
drawbacks at present. One is that I want to pass the username and pw to
the data source at login, and I'm concerned that re-linking tables at
every login will incur a substantial performance penalty (it sure was
linking to an MDB on a file server).
The other is that because of the way permissions work in MySQL and
MyODBC, a user who does not have permission to all columns of a table
gets an error. I'd like to avoid this situation.
Nonetheless, this is something I'll continue to look at. Thanks for
the advice.

Bri wrote:

Eric,

1) I connect to SQL Server via Linked Tables. Once the link is created
you don't need to relink it everytime you open the database. I use the
following function to create the link:

Function Link2ODBC(stTableName As Variant)
Dim tbl As DAO.TableDef, stConnect As String
stConnect = "ODBC;DRIVER=SQL Server; SERVER=myServer; UID=myUID; _
PWD=myPWD;DATABASE=myDatabase"
Set tbl = db.CreateTableDef(stTableName, dbAttachSavePWD, _
stTableName, stConnect)
db.TableDefs.Append tbl
End Function

2) In Jet, the AutoNumber is assigned during the Update and the
recordset continues to point at the added record. In SQL Server the
Identity is assigned when the record is initiated, so it is available
before the Update (which drops the pointer to the added record). I do
it with a DAO Dynaset recordset, but other recordset types may work
similarly.

Jet
===
rs.AddNew
rs!Data = "something"
rs.Update
lPK=rs!PK

SQL Server
==========
rs.AddNew
rs!Data = "something"
lPK=rs!PK
rs.Update

Hope that helps.
---
Bri

[rkc]

"Eric Ellsworth" <ez@e> wrote in message
news:ON********************@speakeasy.net...

Thanks, rkc. What do you do as a workaround for not being able to open
an updatable recordset?

Well, I wouldn't use DAO with MySQL, but I quess using either
dao.database.execute or dao.connection.execute to run an
sql query would be the way to go if I had to.