linking tables/exporting in access95 to MySQL

[doddo]

Hi, I have an access95 DB on a client that I need to link to MySQL to make the data available to a larger audience. This has a number of forms for data entry. When I remove the table and replace it with a link to one on the MySQL DB (which has the same name) the form claims that it cannot find the table. All i have done is move the table onto the MySQL db and create a link to it using ODBC. Does anyone know why this would be?

Also, as an alternative I have tried to export tables directly from the Access db to the Mysql using ODBC. In this case I get a message to the effect:

[TCX][Myodbc] Unknown table 'tablename' (#1051)

It works on every other machine with other databases. I have given this machine access and it will link tables. The only thing on this machine that is different is that you have to log on to access to use it (the db is also replicated). could this be our problem?

any help greatly appreciated
cheers

 

[DougP]

yes Access and MySQL are two different animals

You need to make a new concetion string to open the MySQL db and table

for Access you might have something like so:
<%@ Language=VBScript %>
<%
Set fp_conn = Server.CreateObject(&quot;ADODB.Connection&quot
Set fp_rs = Server.CreateObject(&quot;ADODB.Recordset&quot
fp_conn.Open Application(&quot;customer_ConnectionString&quot '< Created by Front Page in the Global.asa file
fp_rs.Open &quot;customers&quot;, fp_conn, 1, 3, 2 ' 2=Table 1=SQL Statement
%>

This is not going to work with MySQL
you need something like so (this is for SQL Server 7)
<%@ Language=VBScript %>
<%
Set Conn = server.CreateObject(&quot;ADODB.Connection&quot
Conn.Open &quot;driver=SQL Server;server=clh.ij.net;uid=universal1;pwd=ULS1;database=Universal1;&quot;

Set RS1 = Conn.Execute(&quot;INSERT INto....&quot
%>


DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[doddo]

thanks, is this something exclusive to 95 that i have to do because it works fine for all other machines running higher versions of Access with MyODBC drivers? It just seems to be this machine with 95 which won't let me export to MySQL. I have removed the password protection but this doesn't make any difference. Could it be that the DB is replicated?

I haven't had to alter the connection string before do you think i would need to specifically for access95?
If so, how do I get to it?

thanks in advance