How to get Access to connect and use SQL

[tenntazz26]

I am a novice to SQL server. This is the first time I have used it. I managed to make it through the install but now I am having problems figuring out what to do next. I have an Access2000 database that I want SQL server to use. I found an option that allowed me to import the tables into SQL but now I am unsure as to how to get Access to connect or use the SQL server. What do I need to do to accomplish this? Is there something I have to setup or install on the client machines?

Thanks,
Dale
tazzmaniandevil@prodigy.net

 

[tanuki3]

In the Control Panel, click on Data Sources (ODBC). Look at both the System DSN and User DSN tabs to decide where you want to create the data source. Click on Add, select a driver (I use the generic SQL Server driver) and let it walk you through the steps as far as picking your data source, etc.

Once this source is set up, you then open your Access database, File/Get External Data/Link Tables, and choose an ODBC Databases as the file type in the box that pops up. You will then see a box similar to the one in the control panel - click on the Machine Data Source tab. Just choose the source you created there and type in you password (if necessary).

 

[TheWebDataGuy]

Access Project is a wonderful utility that allows you to connect to a SQL Servre DB. You can administer any SQL DB using Access interface (you may not be able to use all the utilities of SQL Server)

In access click on New>Project(Existing Database). Choose the SQL Server you want to connect to and use proper authentication. Now you will see all the SQL tables, views, stored procedures in your access interface together with the standard Access Forms, Reports etc.

DSNs are required for programming VBA modules, recordsets.

Have fun!

 

[tenntazz26]

Thanks for the help. That seemed to have done the trick and now Access is seeing the server. I do have another question on this. After I imported the database into SQL and managed to get Access to see it, I noticed my data was not being updated any longer. It may be do to the design of the program I have the actually writes to the database. Our company uses an old program which is based off of Access 2.0 and the database is a .MDB file. It is protected against doing any type of physical writing to its tables without using the actual software to open it, so I have created my own database which I linked to the original database and it works. The database I have created has all of the forms and queries I want to pull the data I am looking for. In the SQL server I went through the Import process and told it to import the database I created. When I go through the process you gave with Access to connect to the SQL server, it does connect to it and seems to list the queries I created. The problem is the data does not change with what is in the original protected database. In otherwords, I need to know how to import my queries I created in Access and have SQL link those to the original database to run the queries on. Did I miss a step when I did the import or was there a link option I should have chosen?

Thank you,
Dale