URGENT - Connection to SQL Server from Access not working.
-
Hello,
I think I may be going a little bit mad. Last week I created a database on SQL Server. I then created an access database on a different windows server, and set up an ODBC connection on that windows server to my database on SQL Server. I then set up linked tables within Access to point to my tables on the SQL Server. I'm fairly sure that I opened those links at the time and managed to display and update the data from Access. However I have come to this today and when I go into the Access database and attempt to open one of the links I get a connection failed message. When I check the ODBC connection on the windows server it is fine. Does anyone have any ideas on this? I really need this to be working by tomorrow to do a demo, and I'm so convinced it worked last week I am a little bemused!
Many many thanks in advance of your wonderful and prompt replies!!!
-
Have you gone into the ODBC data sources on the Server Access in on, and tested the connection? Try this to make sure the connection is working. Next go into the Access database and try to create a new link to a table on sql. If that works then refresh all your links.
-
Hi, thanks for this. I realised the mistake I made. I had created the ODBC connection whilst actually on the server, but then I tried to open the tables when connected to the server from my client machine (through my network places) and it failed. I realise now that I need to create the ODBC connection on the client machine. Which is a shame as I have many users that will need this!
Thanks for the reply.
-
An ODBC "Connection" is a bit of a misnomer.It is a guide to help you link data. You should be able to link tables using an odc and then use the databse for different users *provided* that you are using Windows Authentication. This is because the generated connection string includes the magic mantra - Trusted_Connection=Yes. Try it and see.
-
I'm sorry, I don't really understand what you are saying. odc is open database connection is it? How do I do that through Access? I thought there was only one way of linking tables in Access.
Thanks,
Paula.
-
There are many ways of attaching tables.
What I meant was that once you've got the tables attached, you don't need the dsn/odc whatever. Using Access 2003, if you hover over a table it displays the connection string. The connection string is not usually filepath\filename.dsn bit rather something like
"ODBC;Driver=SQL Server;SERVER=Servername;DATABASE=DatabaseName;Trusted_Connection=Yes"
This means you can give your users their own copy of the Access Database and it will connect to the same tables etc providing that they have rights to see the tables.
-
Hm, I've tried hovering over the link in Access but I get nothing. I only have Access 2000 though. Should I be able to do this when I hover over the link?
-
Don't know about 2000. I think I got confused because i generally use DNS less connections and connect all my tables in code - lots of it.
Try a search on dsnless connections.
One trick is to put the DSN in a common place and use that. Put the dsn on a server share and navigate there so that it uses a standard location for all users.
-
Ok, thanks. Will do.
-
Follow jfmccabe's advice. Go for DSN-less. If you're interested in that, here's a little code to get you started:
DoCmd.TransferDatabase acLink, "ODBC Database", _
strConnectString, _
acTable, strTableName, strTableName
Here's one possible value for strConnectString:
Provider=SQLOLEDB;DRIVER=SQL Server;SERVER=myservername;DATABASE=mydatabasename;Trusted_Connection=Yes
If you have a list of tables, such as an array or a table of tables, you can loop through that once to get values for strTableName. There are lots of ways you can go at this.
Later, if it comes time to change the connect string on your linked tables, you'll want to investigate this:
dim dbs as dao.database
dim tdf as dao.tabledef
Set tdf = dbs.TableDefs(strTableName)
tdf.Connect = strConnect
tdf.RefreshLink
We manage about 200 tables, regularly running against different databases (Development, Live, Testing, Training) using exactly this system.
"DSN's!? We don't need no stinkin' DSN's!! Ha! Ha! Ha! Ha! Ha! Ha!"
Good luck.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply