connection string for access FE and mysql BE

[Guest]

How do I open a record set with the BE in a mysql table on a server? I assume
my problem is in setting the connection string
For example:
The current code in the FE (that no longer works) has the below
Dim cnn As New ADODB.Connection
Set cnn = Application.CurrentProject.Connection

thanks,

 

[Pieter Wijnen]

I think (ok, I know) CurrentProject.Connection is restricted to ADP's with
MSSQL as backend

Pieter

 

[Albert D. Kallal]

Well if you successfully linked the tables, then you can just go:


dim rst as dao.RecordSet

set rst = currentdb.OpenreocrdSet("name of talbe")

 

[Hans]

Just go to MySQl site, there are (references to) examples of how to connect
to MySQL in VB(A).

Yours friendly,

Hans Heezemans
(www.iservi.com)

 

[Guest]

Albert,

I am still a bit lost. I suppose my question really pertains to the
usefullness of FE VBA code if the mysql tables are on the back end being
accessed using an Access FE through an ODBC connection. Will the FE code
continue to work or do i, for example, now have to make specific reference to
the BE server or something in the connection string.

my forms are being populated by data in the mysql tables so i suppose all is
linked up and working.

thanks,

 

[Albert D. Kallal]

Albert,

I am still a bit lost. I suppose my question really pertains to the
usefullness of FE VBA code if the mysql tables are on the back end being
accessed using an Access FE through an ODBC connection. Will the FE code
continue to work or do i, for example, now have to make specific reference
to
the BE server or something in the connection string.

my forms are being populated by data in the mysql tables so i suppose all
is
linked up and working.

Yes for the most part everything will continue to work as before. About the
only area to watch is to make sure that all tables have a primary key and
also that all tables have and expose a time stamp field.

You'll find in most cases about 90% are more of your code will work as the
as unchanged.

however often you'll have to force the describe to get the PK value

eg:
Dim rstRecords As DAO.Recordset
Dim lngNext As Long

Set rstRecords = CurrentDb.OpenRecordset("tblmain")
rstRecords.AddNew

' code can go here to add data, or set values to the
' reocord...or, you could just use this code to "grab"
' the id of the reocrd added..and then use a update query (not a
' instert query) to update your values.

msgbox "PK autoid = & rstRecords!ID <----- will not work with sql
server!!!!!

rstRecords.Update

So, you must FIRST execute teh rstReocrds.Update, and then go:

rstRecords.Bookmark = rstRecords.LastModified
lngNext = rstRecords!ID
rstRecords.Close
Set rstRecords = Nothing
MsgBox lngNext

So, autonumber PK untill AFTER you execute a reocrd write. The code thsu
beocmes:

Set rstRecords = CurrentDb.OpenRecordset("tblmain")
rstRecords.AddNew
...do whatever
rstRecords.Update
rstRecords.Bookmark = rstRecords.LastModified
lngNext = rstRecords!ID
rstRecords.Close
Set rstRecords = Nothing
MsgBox lngNext

So, force the disk write BEFORE you grab the PK.

This problem also exists for a form. However standard sub form should
continue to work as before (this is because MS access to always executed
disk write when the focus changes to a sub form - even with a JET back end).

However if you have some code in the actual form that does need the PK then
you'll have to execute a disk write.

You can use:

old:

lngPK = me!PKfield

New

if me.Dirty = true then
me.dirty = false
end if
lngPK = me!PkField

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(e-mail address removed)


discreet or force one