How do you link tables between databases? 1

[Guggly]

Hi! Can someone tell me if there's any way to create a symbolic link on a table between two databases? I can do this with MySQL and Microsoft Access, but the only way I've figured out to do it with SQL Server is via a SELECT statement. Is there some other way to do it, so that it acts like a linked table in the second database? Thanks! -- Mike

 

[gbs01]

How did you link your Mysql to your Access tables? I have a need to work with mysql data on a regular basis, but I would like to do this in Access.

- The data is generated & stored in mysql.
- Nightly I plan to link or copy the data to Access
- From Access I will query, chart & graph the data.

Thanks in advance for your help,
jlig

 

[Catadmin]

You can register Access as a Linked Server in Enterprise Manager. Then, when you run queries, use a fourpart naming conventions: Servername.Databasename.Owner.Tablename

But other than Linked Servers or multiple SQL Server instances, I've never seen any "symbolic links".

Check out Books Online for more details about this subject.



Catadmin - MCDBA, MCSA
"Just because I'm paranoid doesn't mean the universe *isn't* out to get me!"

 

[gbs01]

Ok, I don't use mysql at all right now, but I use Access 2003 all the time.

Can't I just:
1) export the mysql data to a meaningfull text file,then
2) import the text file into Access

Right now I import text files all the time using the DoCmd.TransferText in Access.

Then I use Access to print out beautiful reports.
Thanks again,
jlig

 

[Guggly]

gbs01, what you are describing should work fine (export data from MySQL and then import it into Access), but you can connect to a MySQL table directly via ODBC if you install the MyODBC driver. See the link below for more information:

http://dev.mysql.com/downloads/connector/odbc/3.51.html

If needed, you should be able to find plenty of examples on using MyODBC with Access.

Hope that's a help! -- Mike

 

[gbs01]

Thanks!