MySQL as a backend and Ms Access at a frontend.

[nim180]

Hi everyone,

Ok im hoping someone can help me with the following. Ive been reading up on using MySQL as a backend and MS Access as a front end. Now ive downloaded the MyODBC driver onto my local machine along with mysql for testing. I have transfered all the tables from my database to mysql and have linked them. This all works perfectly and i am able to use all my forms on the access frontend and it updates the mysql backend.

Now this is fine as everything is on my computer. Heres where my situation begins which im hoping someone can advice me or give me a step by step run down. There are about 10 other users who need to use the database from their computer and be able to save the information into the same mysql back end. So in otherwards everyone will have an instance of the access frontend but all the information will link into the same mysql backend on a server. Ive been told by my IT Admin that this cannot be done but ive read on many different places on the web of people achieving this so i know it can be done.

Im gathering each computer will need to have the MyODBC installed. Is there any chance someone can give me a step by step of how to do this. Thanks in advance for your help.

Regards,
Nim180

 

[galorin]

I have a similar setup where I work. In testing we have had no problems with multiple-user access. Here is how I connect to the database, which may have something to do with our results.

This all goes within the Modules>functions section

Public rst As ADODB.Recordset
Public cn As ADODB.Connection
Public cmd As ADODB.command
Public SQLout As String 'Get data out of SQL
Public SQLin As String 'Put data in to SQL or modify data

[COLOR=green]'determining locality, and server string[/color]
Public Const islocal = False
Public Const DB_CONNECT As String = "Driver={MySQL ODBC 3.51 Driver};Server=192.168.101.14;Port=3306;Database=contacts;User=remote-user; Password=password;Option=3;"
[COLOR=green]'Public Const DB_CONNECT As String = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Port=3306;Database=contacts;User=remote-user; Password=password;Option=3;" 'used for connecting to read-only local cache[/color]
Option Compare Database
-----------------------------
Public Sub connectDB()
[COLOR=green]'Connects to database above
Set cn = New ADODB.Connection

With cn
  .ConnectionString = DB_CONNECT
  .Open
End With

Set cmd = New ADODB.command
With cmd
 .CommandType = adCmdUnknown
End With

End Sub
Public Sub getData()
[COLOR=green]'Sub to get results off the server[/color]
With cmd
  .ActiveConnection = cn
  .CommandText = SQLout
  Set rst = .Execute
End With

End Sub
Public Sub putData()
[COLOR=green]'Sub to enter data into the database.  Note, does not do date interpolation[/color]
With cmd
 .ActiveConnection = cn
 .CommandText = SQLin
 .Execute
End With

End Sub

 

[DonQuichote]

Off course this will work. Be aware though, that if you link a table from a database server, MS-Access by default tries to resolve the queries locally. In some cases, it can only do so by downloading the whole remote tables. This can be very slow. To overcome this, you can mark a query as a "pass-through query". Pass-through queries are parsed and executed by the server instead of MS-Acces itself.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[nim180]

Hi Galorin and DonQuichote,

Thanks for your response. Galorin i hope you can bare with me on this one, im not to good with servers, networks etc and im not getting much help from the IT admins's so i hoping you can give me a run down. The code you have given me, im guessing i place this code into each instance of access front end. Am i correct in saying the only information which i need to replace in the code is the MySQL info ("Driver={MySQL ODBC 3.51 Driver};Server=192.168.101.14;Port=3306;Database=contacts;User=remote-user; Password=password;Option=3;")

Im gathering i can give the Module any name yes. Once this module is placed into the DB do i need to do anything else, do i need to call the module some place else. Also do i need to install the MySQL Driver onto each computer.

Once again thank you for your help i dont know much about any of this so any help would be great.

Regards,
Nim

 

[galorin]

The only portions that need changing for my method are:

Server=
Database=
User=
Password=

these values need to be changed to reflect your own configurations. Server can be a DNS-resolvable name, but I prefer IP addresses.