?
Solved

create a mirror of MS SQL table in Access or MYSQL

Posted on 2006-05-26
13
Medium Priority
?
1,230 Views
Last Modified: 2008-03-06
is it possible to create a mirror of a table stored in MS SQL server 2000 in ODBC or MySQL?

so if info is updated in table stored in MS SQL, it will be automatically updated in mirror version...

hope you understand?
0
Comment
Question by:ellandrd
  • 8
  • 5
13 Comments
 
LVL 4

Expert Comment

by:ch70357
ID: 16769452
For MS Access, you can set the SQL table up as a linked table, or create an ADP (Access Data Project) instead of an MDB to connect drectly to the SQL server. As for the MySQL I am not familiar with that, but I would recommend researching using the ADP method.
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16769508
ok ms access sounds good. if you are unfamilar with mysql is it fine.

next thing is im unsure on how to do what you suggested...so can you help me do this please?
0
 
LVL 4

Accepted Solution

by:
ch70357 earned 2000 total points
ID: 16769572
Sure, Open ms access, choose new, select Project Using Existing Database, Then you will have to enter the Server Name or IP Address, enter user name and password, and then test connection.. Close, save the password if needed. You will see all the SQL Server tables in the SQL Server database you have selected as well as stored procs and more. This is a FANTASICT option provided by MS Access.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 16

Author Comment

by:ellandrd
ID: 16769645
>>Sure, Open ms access, choose new, select Project Using Existing Database, Then you will have to enter the Server >>Name or IP Address, enter user name and password, and then test connection..

i dont have this!  i can create new project suing existing database, but i only allows me select a .adp from explorer...

where do i enter server name?
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16769667
sorry i got working... how do i save this mirror then?
0
 
LVL 4

Expert Comment

by:ch70357
ID: 16769714
This adp is connected directly to your SQL server, so any updates to the table on the server will be reflectd. Remeber, whatever user name and password that you used, those permissions in the adp will be exactly the same as on the server. So, if the users using the table need limited permissions, you may need to create a new logon to the server.
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16769733
ya ok, but how do i save this so i can connect using ASP or PHP
0
 
LVL 4

Expert Comment

by:ch70357
ID: 16769784
Ok, I got the impression from the question that you simply need to ensure that users viewing data saw updates that occured on the servia via an MS Access front end cient. I am definately not an ASP guy, so maybe someone else can help with this. BUT, is there any reason the ASP can not connect directly to the SQL Server through VB Script?
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16769834
>>BUT, is there any reason the ASP can not connect directly to the SQL Server through VB Script

have tired and tired and keep failing - i have opend about 15 questions over last 2 days looking for help and gone fu*k all that would fine...

so my plan is to see if i can replicate the MS SQL server to an MS Access DB as i can connect to a  MS Access DB with ASP and PHP fine...

see what im trying to get at?

maybe i should have asked how to i replicate the SQL server instead of a mirror...

Mmmm

can you tell me how to replicate SQL server 2000 into a MS Access DB ?  if so i will accept this and open a new question for you...
0
 
LVL 4

Expert Comment

by:ch70357
ID: 16769946
Try this:

Start --> Settings --> Control Panel --> Administrative Tools --> Data Source (ODBC) and set up a DSN.

Create a blank .mdb, go --> File --> Get External Data --> Link Tables

In the File of tpye select the last option (ODBC Databases) --> Select the DSN you have just set up

Then select the table you want to link to. The table is linked via ODBC directly to the sderver, so any updates will reflect automaticly. It is not replication (im not sure how to do this.), but it may work just as well. Hopefully this helps, I know what its like to dig for an answer and not be able to find it.
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16769961
what about stored procedures - can i not just copy everything...
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16770093
ok i got it all working just how i want it... thank you so much!

i will accept your comment for the mirror thingy and i will open another question for you as you gave me a solution for a kinda replication... too..

thank you!
0
 
LVL 16

Author Comment

by:ellandrd
ID: 16770130
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

755 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question