473,381 Members | 1,425 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,381 software developers and data experts.

DSN-less ODBC questions

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
Nov 12 '05 #1
13 8240
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.

Nov 12 '05 #2
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.


Nov 12 '05 #3
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.
Nov 12 '05 #4
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


Nov 12 '05 #5
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


Nov 12 '05 #6
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.

Nov 12 '05 #7
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)

Nov 12 '05 #8
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)

Nov 12 '05 #9
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
Nov 12 '05 #10
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)

Nov 12 '05 #11
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.

Nov 12 '05 #12
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

Nov 12 '05 #13
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.


Nov 12 '05 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: emily | last post by:
Perhaps this has already been answered. If so, I apologize. I'm a newbie at this stuff. I have a DTS package to import some Visual Foxpro data. Yes, I said Visual Foxpro. :) The DTS package...
3
by: F. Michael Miller | last post by:
I can't find the file dsn for an Access DB. I can refresh the tables, and I've looked for all of the *.dsn files on the drives where it might be and I can't seem to find it. Is there any way...
3
by: Zlatko Matić | last post by:
Hi! What happens with linked tables if they were linked using File DSN, when I copy the Access file on some other PC without File DSN ? What is the difference between DSN on linked tables and...
4
by: Steve Sweales | last post by:
I'm trying to find some code on how to create a DSN using C# and SQLConfigDataSource. Can anybody help me please, before I tear my hair out!! *** Sent via Devdex http://www.devdex.com ***...
3
by: Niks | last post by:
Hi, I need to connect to SQL server Database using a System DSN. Can anyone tell me how to connect to SQL Server using DSN in ASP.NET (VB.Net). Using a Try Catch block. Does anyone know how to...
6
by: Shibu | last post by:
Hi, Can anyone tell me how to create a DSN from asp.net.. Thanks Shibu
14
by: kdv09 | last post by:
Hi! I'm looking for some help in fixing my screwup: I've got C++ app which reads MDB database, using MFC CDatabase and CRecordset derived classes. I had it working OK on development PC (Win XP...
0
by: alingsjtu | last post by:
What's the DB2 connection string wihout DSN when use IBM DB2 OLE PROVIDER in windows environment, such as ADO or Creation of Linked Server in MS SQL Server ? I searched for a long time to look...
0
by: tezza98 | last post by:
Hi im using VBA to create A MYOB_ODBC User DSN here is my code Option Compare Database 'Constant Declaration Private Const ODBC_ADD_DSN = 1 ' Add data source Private...
4
by: bytesbytes | last post by:
Hi, Im using VB6.0, Crystal report 10 and MSaccess Database. I was using Crystal report 6 and later upgraded to CR10. In VB6 i used DSN like .Connect = "DSN=DBName" & ";UID=admin;PWD=;" Now in...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.