link tables from SQL Server

[Kevin_S]

Hello everybody! (Win2000/Access 2002/SQL Server 2000)

A quick question:

I moved a database b/e table structure into SQL 2000. I then created a new db and linked in these tables - - everything works fine for me BUT... when I e-mail the db to someone else they get an error message telling them that the ODBC connection call failed to the server....soooo.....

I took the original db - ran the upsize wizard - choose to link the tables in when given the option - completed the upsize to SQL. everything works fine for me again BUT...when I e-mail the db to someone else theyI get an error message telling them that the ODBC connection call failed to the server....ok soooo.....

(very confused...)

HOWEVER:
IF I take the same app. - run the upsize wizard - choose the option to have the wizard create a new access project front-end for the application - finish the process and send it around everyone connects with NO PROBLEM?!?!?

I don't understand why this is occuring - I thought it might be something to do with permissions to the server but this would effect the adp front end as well so that can't be it...

Am I missing something simple here?

Any/all insight on this is appreciated.

Thank you,
Kevin

 

[FoFa]

Check your connection strings in each instance, I bet that is where the differance is.

 

[Kevin_S]

Hi FoFa - thanks for the reply

I forgot to add in the first post that I am a real newbie when it comes to SQL Server so checking the connection strings is something I am not entirely familiar with. I will do some investigating on this but if you have a few minutes and could possibly explain how I would go about doing this I would appreciate it

I find SQL Server to be very interesting (stored procedures are going to help our db's network preformance immensily) and I am excited about its use but my experience, as stated before is limited and my training on this is not until two weeks from now and I need to get some things started into SQL Server today! (isn't this always the case)

Thanks gain for your help,
Kevin

ps. I want to use an mde file as my front end and not an adp file because I plan to keep a few look up tables in access with the front ends as they don't need to be in SQL.

 

[crosmill]

Projects looks for specifically for the SQL server this is why it 'uncharacteriscally' keeping the connection. If you move the location of the Database then the Linked tables will have a different path and therefore the ODBC will fail.

When you email the database you will need to relink the tables from the SQL Server.

 

[FoFa]

Well since I have not done what you did, I am guessing, but there are a number of ways to access external Db's (SqlServer in this case). You can setup an ODBC connection on each workstation, than reference that. You can use, um, I forget the name, but basically it is a FILE DSN defined in the module and NOT in a typical ODBC connection so an ODBC connection is not setup on each Workstation. My guess is the first two were trying to use a Worklstation ODBC connection that you maynot have setup on each Workstation, and the last option used an internal connection that did not require an ODBC DSN on each workstation. Usually your connection strings will define this.

 

[crosmill]

I just remebered, SQL Server works on an 'assign each user' basis not 'allow all' by default, Access project may set up it's own user account when you create the db, if the tables are just linked then you have to setup a user account in SQLServer for each NT user account that will be accesing it.

 

[Kevin_S]

Thanks for the replys:

ok - from the top - -

If you move the location of the Database then the Linked tables will have a different path and therefore the ODBC will fail.

crossmill - After sending the application to different users I went into their front ends and tried to relink but for some reason they are not able to "view" the server where the db is residing???
- is this a problem with them having permission (I don't think so) or do I have to set them up to "see/view" the server????

--Also - I have eliminated the possiblity of this being an issue of permission as I am using SQL server side security password and not NT security...

FoFa - the connection strings are indentical on both the differnet machines and the different apps (adp and mde)

Any other suggestions....
Please Help!!!

Kevin

 

[crosmill]

Is SQL Server on another domain? Do they have permissions to see that domain?

 

[Kevin_S]

Thanks for the continued interest/support crosmill -

Unfortunitly I am again going to have to confess that this SQL/server/Connection String/Domain stuf is currently a little over my head - (I'm a fast learner though!!! )

Is there a way I can check/confirm from my workstation weither or not the SQL server is on a domain that they can see?

Thanks again,
Kevin

 

[FoFa]

Does each workstation have the SqlServer drivers loaded?

 

[crosmill]

To be honest I'm not sure how it works myself, I'm not sys admin.

But go to Network Places and check if the server is in there. I don't think it will be, but I think thats where access would check for the server.

Someone else will have to step in and tell you how to connect them to it if their not, unless your sys admin can help you........?

 

[Kevin_S]

Thanks to both of you for your help on this!

I am going to sit down with our Departments DBA and work this out with him....

I'll post back the solution (for others to maybe gleen some knowledge off of) when i find out the answer...

Thanks again,
Kevin

 

[Pat Hartman]

Look at the following articles (plus do your own search) for possible explainations/solutions.

Q174655 - PRB File DSN and DSN-Less Connections May Fail with Localized ODBC

ACC Procedure to Create Data Sources and Relink ODBC Tables (Q159691)



Other articles that should interest you:

ACC2000How to Simulate AutoLookup w- Stored Proc in Access C-S

Q124901 - ACC How to Trap ODBC Login Error Messages

Q208858 - ACC2000 Optimizing for Client-Server Performance

And finally, don't forget that forms based on stored procedures are NOT updateable.

 

[Kevin_S]

Thanks Pat - (I am actually over at the Microsoft Site looking up articles as we speak)

Thanks for the articles - I'll give them a read!

 

[Charmed7]

I may have an answer

I know this is an old post, but it seems to get a lot of views, so I thought I would put what worked for me, in case there are still people without answers.

I had the database on a share drive in my work, and when someone from another workstation open it, the linked tables were giving the ODBC Connection to (AM_NT) failed.

For the workstation that didn't work, we followed these steps:

Open Control Panel for the computer
Choose Administrative Tools
Choose Data Sources (ODBC)
Choose tab System DSN
and Add new Datasource

If you have a work station that does work, follow through the same steps and see what the configuration is and match it for the non-working work station. It should be pretty self explanatory from there. I'm very novice when it comes to things like this, so I have faith in all of you too.

Goodluck and have a fruitful day.