from an adp link to other information stored in a MySQL database

  • Thread starter Thread starter Sean Moore
  • Start date Start date
S

Sean Moore

I have used an ODBC connection to link an MDB to MySQL, but I also have an
ADP program connected to an MSDE database, but need to also fetch addtional
info fom a MYsql datgabase. I have been unable to establish such a link.
Can someone point me at an explanation for how to do this?
 
you need to install mySql ODBC drivers
and then proabbly set up a linked server between the MSDE instance and
the mySql machine.

it should be almost as easy as right-click link.

but you'll probably need mySql odbc drivers on the server; and probably
on your desktop also
 
You can't do this directly in an ADP, unfortunately. It may be possible to
do it on the MSDE side, or you can link the SQL Server tables into the same
MDB as you have your MySQL tables.


Rob
 
We attempted to create a link server between an MSDE database and MySQL 4.1
database using ODBC. However, whenever we attempt tpo look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interface
error: Cocreate of DSO for MSDASQL returned 0x800700005].

When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).

ANy direction or source of specific aids would be appreciated.
 
do you have the myODBC drivers installed on the MSDE machine?

do you have mySQL ODBC DSN connectivity?

setup a DSN and take it from there; it should be cake

-aaron
 
In order to use "Right Click -> Link Tables" from MS Access, you have to do
it from the machine that MSDE is installed on. You do not need ANY extra
drivers on the workstations that will be access the database as MSDE will do
all of the data access to the "remote" mySQL server. You will use 4 part
table names in a view or Stored procedure to access the MySql data. If you
haven't already, you should download the BOL (Books On Line) for MS SQL
Server (for what ever version you are running, mostly likely 2000), look in
the Index for "Linked Server." I'm pretty sure they will even tell you how
to do it from TSQL Code. It's a free download from Microsoft's Website.

If you have access to Enterprise manager, that's actually the easiest way to
establish the link, as long as you know the connect string.

Having all of the data access happen at the server level is really nice as
you don't have to worry about direct client access to the different data
sources you need to use, which means you don't have to setup connections at
the workstations, JUST at the server! It's a beautiful thing ;-)

Mark S.
 
btw, do you have the default port open on the mysql box? i've had a
bear of a time with those types of things recently lol

can you connect via odbc by setting up a DSN?

aren't DSNs faster than other ways?

-aaron
 
We have had some success, but different problem which may be essentially the
same.

On a different machine which has MSDE , loaded a MySQL 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string MYSQL 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}

After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables in
the MySql database was visible.

I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the Mysql databas3e when I executed the
statemnet:

Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')

End of success.

i started up an Access ADP program and attempted to add external data using
the Linked Table wizard. After selecting tbl1 it created an odc file, which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. Was this the right way to gain
access to these MySQL tables? Is there something else that I am missing?
If you have had experience with this aspect, a comment would be graciously
accepted. Thank you.



Sean Moore said:
We attempted to create a link server between an MSDE database and MySQL 4.1
database using ODBC. However, whenever we attempt tpo look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interface
error: Cocreate of DSO for MSDASQL returned 0x800700005].

When I searched the web for this message, I learned that it is not unusual
to encounter it, but have not been able to find a way to get past it. The
version of MDAC appears to be consistent with the Microsoft table relating
version and Windows version (2000).

ANy direction or source of specific aids would be appreciated.
Robert Morley said:
You can't do this directly in an ADP, unfortunately. It may be possible to
do it on the MSDE side, or you can link the SQL Server tables into the same
MDB as you have your MySQL tables.


Rob
Click to expand...
Click to expand...
 
It looks like it could be a SQL Server security feature.
"cross database chaining" has the right kind of sound to
it: I'd be looking for information on that.

(david)


Sean Moore said:
We have had some success, but different problem which may be essentially
the
same.

On a different machine which has MSDE , loaded a MySQL 4.1 database. When
adding the ODBC/Connector used the FTP address of this machine in the
block
for Server. tested the connection succesfully.
Then used query analyzer to EXEC sp_addlinkedserver with
New LinkedServer Name,
string MYSQL 4.1,
string MSDASQL,
the name of the ODBC/Connector from first step,
Null,
string Driver={MySQL ODBC 3.51 Driver}

After execution of this I started up Enterprise Mgr and could click of the
Linked Server name, and then the tables Icon and a list of all the tables
in
the MySql database was visible.

I went back to the query analyzer and executed a select statement and saw
data from field1 in tbl1 of the Mysql databas3e when I executed the
statemnet:

Select * from OPENQUERY( new linked servername, 'Select field1 from tbl1')

End of success.

i started up an Access ADP program and attempted to add external data
using
the Linked Table wizard. After selecting tbl1 it created an odc file,
which
when I attempted to open it was greeted with the error message: This
DataSource contains no visible tables. Was this the right way to gain
access to these MySQL tables? Is there something else that I am missing?
If you have had experience with this aspect, a comment would be graciously
accepted. Thank you.



Sean Moore said:
We attempted to create a link server between an MSDE database and MySQL 4.1
database using ODBC. However, whenever we attempt tpo look at the tables
associated with the link we are greeted with the message: Could not create
an instance of OLE DB provider 'MSDASQL'. OLD DB error trace [Non-interface
error: Cocreate of DSO for MSDASQL returned 0x800700005].

When I searched the web for this message, I learned that it is not
unusual
to encounter it, but have not been able to find a way to get past it.
The
version of MDAC appears to be consistent with the Microsoft table
relating
version and Windows version (2000).

ANy direction or source of specific aids would be appreciated.
Robert Morley said:
You can't do this directly in an ADP, unfortunately. It may be
possible to
do it on the MSDE side, or you can link the SQL Server tables into the same
MDB as you have your MySQL tables.


Rob

I have used an ODBC connection to link an MDB to MySQL, but I also
have an
ADP program connected to an MSDE database, but need to also fetch
addtional
info fom a MYsql datgabase. I have been unable to establish such a link.
Can someone point me at an explanation for how to do this?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top