Re: Access 2007 and MySQL
- From: "Albert D. Kallal" <PleaseNOOOsPAMmkallal@xxxxxxx>
- Date: Sat, 22 Nov 2008 15:46:38 -0700
"Robert S." <robsmiler@xxxxxxxxxxxxxx> wrote in message
news:gg9ees$7bc$1@xxxxxxxxxxxxxxxxxxxx
Just being curious:
Anyone tried MySQL as backend for recent Acc2k7 FE developments?
Valueable comments what works, what don't, what to having an eye on, are
highly appreciated.
rob*
Most of us here have experience using access with the several free editions
of SQL server. Furthermore the upsizing wizard in access don't work with
other servers. (I suspect mySQL people probably have something put together
that will import and set up the tables for you, but you'll have to ask in
their newsgroup).
Most of us prefer using linked ODBC tables to sql (or in your case MySql).
You can however if you want choose SQL server for your back end and then you
can use what is called a access data project (adp). This allows you to go
into table design mode inside of ms-access and the tables + changes in
desing are made to the actual tables sitting on SQL server.
If you don't use an adp project and just use linked tables via odbc, then
you'll have to use the server side design tools to make changes to the table
structure.
For the most part most your recordset code and most queries should work as
is. About the only thing to look for is code that needs/uses the autonumber
right after a record is added.
for recordset code you get:
Dim rstRecords As DAO.Recordset
Dim lngNext As Long
Set rstRecords = CurrentDb.OpenRecordset("tblmain")
rstRecords.AddNew
' with access you can grab/use the autonumber at this point in time
' with sql server, mySql etc, you MUST FIRST save the reocrd
' and go:
rstRecords.Update
rstRecords.Bookmark = rstRecords.LastModified
lngNext = rstRecords!ID
rstRecords.Close
Set rstRecords = Nothing
MsgBox lngNext
So, the above shows you how to get the autonumber id in code if you using a
server based system and dao recordset code. For ado, you can just save the
record and grab the id without having to use the bookmark as per above.
Once you have all your tables linked you then look at some forms and reports
that potentially will run slower then before. Keep in mind that moving to
SQL server or mySQL or whatever will not solve performance problems due to
bad designs. We see frequent posts in the SQL groups were people upsize
their data to SQL server and find things run a lot slower.
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx
.
- Follow-Ups:
- Re: Access 2007 and MySQL
- From: Tony Toews [MVP]
- Re: Access 2007 and MySQL
- From: David W. Fenton
- Re: Access 2007 and MySQL
- References:
- Access 2007 and MySQL
- From: Robert S.
- Access 2007 and MySQL
- Prev by Date: Re: Read from a text file?
- Next by Date: Re: How to change a record in VBA code in combination with a Form editing
- Previous by thread: Access 2007 and MySQL
- Next by thread: Re: Access 2007 and MySQL
- Index(es):
Relevant Pages
|
