January 2, 2008 at 12:36 pm
I have inherited a database of SQL files on a server. I know how to use acces to get at the data but don't know much about the sql connections.
We have a new computer that has the access strucure on it but I need to connect it to the SQL files on the server. I can bring up the SQL Management Studio and see the databases I need but can't figure out how to link them to the computer so I can see them in the Administrative tools ODBC connections. Can someone please help? Thanks.
January 2, 2008 at 12:54 pm
You add a new conneciton in the Data Sources (ODBC) control panel. Enter the server name/instance name and your credentials. It will connect.
January 2, 2008 at 1:42 pm
Thanks. I tried it. The tables then show up with the structure but if I try to open any of them I get an ODBC connection failure. Then if I try to link to the files in the databases using the File/Get External Data/Link Tabes/Machine Data Source - all I see are the schema tables for the database to link to - none of the data tables. So when I did the ODBC connection I guess I didn't really connect to the database I wanted.
It seemed to me I had to do something with the SQL Management Studio in the past but I don't recall what it was.
January 3, 2008 at 9:06 am
Steve - you got something else I can try. I'm really in the ditch here. Thanks.
January 3, 2008 at 9:25 am
Have you confirmed that you have the correct permissions to view the tables that are missing? Make sure that the login you are using, in ODBC, has the proper permissions for ALL tables needed. If you are logging in with your username and password, open SSMS and confirm that you can see the tables and have the correct permissions.
January 8, 2008 at 10:12 am
Thanks Adam but I really don't know anything about SQL, I'm just trying to get this computer with the Access structure to be able to see the files. It worked before the computer was replaced and the new computer has the same username and password so I assumed that was already set up.
Checking the SSMS, I can see that there are 2 logins plus the Adminstrator but we have about 8 machines with different logins in the office that all have access to the data so I don't really get that part either.
I just want to connect the structure on this computer to the data but when I do that using ODBC connect and name the database I want, all I get are structure files (no data files) for that database so I assume it can't see it and don't know how to make it connect.
January 8, 2008 at 11:12 am
It worked before the computer was replaced and the new computer has the same username and password so I assumed that was already set up.
This does not necessarily mean that the ODBC connection string was setup using the same username and password. The ODBC username and password is probably different than the computer username and password. Confirm with whomever set up the machine that the same ODBC username and password was supplied when creating the ODBC DSN. If you created the ODBC connection then trying logging into the SQL server with that same login and expand each database to make sure you can see the tables.
I can bring up the SQL Management Studio and see the databases I need but can't figure out how to link them to the computer
Access is not meant to be a replacement for SQL server. You cannot link databases to ACCESS but you can link SQL tables. If you have to manage, more than one database linking from ACCESS is going to be more cumbersome than just sucking it up and learning SSMS. ACCESS and SSMS are very similar, as both have a query builder and relationship builder.
Other things to look at if you must use ACCESS:
What version of Access are you using?
Which driver is your ODBC connecting with?
January 9, 2008 at 2:21 am
I have used this trick in versions of Access up to 2003 - I haven't tried in 2007 though.
Go onto a machine that works normally, open the Access database. Turn on the view system objects and view hidden objects options, which are found in Tools->Options, on the View tab under Access 2003.
Look for an Access table called MSysObjects. Scroll down the table, looking in the Connect column, which you will need to widen to show the contents. Often you can see unencrypted SQL Server connection strings in there, which should give you the info you need to key in on your own PC when you create your ODBC connection by hand. If you are lucky you will see login credentials there.
Also, it sounds like you are using SQL Server 2005 so when you create your ODBC driver, pick the SQL Native Client instead of SQL Server from the list of data sources. If you do not see the SQL Native Client listed in the ODBC wizard, you may need to download and install it from Microsoft. It is free.
Good luck!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply