MS Access Issue !!!

[SamDazz]

One of my user gets the following error from MS Access running from his local machine and linking to the database & appropriate tables. The MS Access application uses back end SQL server to get info from. ODBC connections are fine, I tested them already. His machine uses DHCP to connect to the network. One thing I'd like to mention is: We have Citrix server on which this application is published and this user can connect and perform the same tasks using the Citrix client. He is having errors in connecting to the tables when he launches locally. Recently he got new upgraded PC and since than this problem arose. He was OK on his older PC. Any ideas what & where else to look?
Thanks

 

[Welshbird]

Did you try the linked-table manager, select all and the rebuild all the links? I think I have had a similar issue and this solved it for me.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SamDazz]

The weird thing is that other users have no problem at all using this from their local machines. This user got his new PC last week and now we are stuck with this problem. He can access the tables using Citrix Client.

PS: Where I can find those links to perform the tasks you mentioned. Can you direct me to them.
Thanks

 

[Welshbird]

Tools> Database Utilities> Linked Table Manager>

then Select ALL, and tick the 'Always prompt for new location'

That way, you get to select the DSN for the ODBC.

Let us know how you get on.



Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SamDazz]

I asked the user to try launching the same application from a different PC, he is succesfully able to get to the databases and tables with no errors of any kind. It must be his PC that needs some tweak. Do you have any idea where and what to check?
Thanks

 

[Welshbird]

I have found that relinking the tables needs to be done from EACH PC. Did that work from his?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SamDazz]

Well the problem is: This application is very restricted and all menus are grayed out except File, Edit and Help. So there is no way of doing anything on the MS Access Application. There must be a tweak which we should do on his PC i.e. some network settings or some other tweak.
The new machine I built had a fresh copy of XP with MS Office 2003 SP2 installed on it, he had no issues with linking tables and getting to the database using ODBC. When I deleted the ODBC connection; he didn't get this error of Linking Table but the other one which says:
ODBC Connection to "DataBase123" failed.

So this shows that there is some Windows Registry Entry or something else that is stoping the connection out from the PC.

 

[Welshbird]

Try opening the application whilst holding down [SHIFT] - that might allow you into those menus.

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[SamDazz]

This doesn't help either, I can get to the menu but still can't get to the ODBC properly, so it trims the problem down to the ODBC Connection String. I can get this user to Dev. environment using another connection string which pretty much the same form just different server but no in Prod. I checked in the registry under:
HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI

There are couple of entries and my Prod entry looks like this:

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Connection2Prod]
"Driver"="C:\\WINDOWS\\System32\\SQLSRV32.dll"
"Server"="ProdServ"
"Database"="master"
"LastUser"="User1"
"Trusted_Connection"="Yes"

This connection doesn't work although the Connection's Test runs successfuly at the end when I create a new ODBC connection.

The other ODBC connection (working connection, Dev. Environment) registry shows like this:

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\Connection2Dev]
"Driver"="C:\\WINDOWS\\System32\\SQLSRV32.dll"
"Database"="master"
"LastUser"="User1"
"Trusted_Connection"="Yes"

As you can see there is no Server name in this registry entry of Server Name in the Dev ODBC connection.

I tried adding/deleting certain things in the registry and create/delete the ODBC connections but nothing works. I can see when I create the Prod ODBC connection it shows me the database 'master' already choosen & grayed out but in Dev ODBC connection it shows with a check mark and it gives me a choice of different databases which should be the way in Production as well.

Any suggestions

What could be done at this point. Its really annoying but hopefully we'll get the resolution soon.

Thanks

 

[SamDazz]

Please post any other suggestions. Thanks.

 

[LNBruno]

Maybe an over-simplification, but have you tried deleting the MS Access application and reinstalling?

< M!ke >
Your right to an opinion does not obligate me to take you seriously.
- Winston Churchill

 

[ck1999]

what version of access are you using


ck1999

 

[SamDazz]

MS Access 2003 SP2. I didn't reinstalled it. What I'm suspecting is ODBC connection. Any more suggestions about ODBC? Does ODBC connection gets installed with MS Access or via MDAC? If via MDAC, what is the way of unistalling MDAC from computer and reinstall it so that it can put a fresh copy of ODBC connection on the computer and maybe that leads us to success?

Thanks

 

[Welshbird]

I suspect that if you are using XP sp2 then MDAC is pre-installed, so that really shouldn't be the problem.

You say you deleted and re-built the ODBC connection, presumably when you tested this it did link ok?

Are you sure you are using the correct ODBC drivers? When you select the MICROSOFT ACCESS .mdb in the ODBC window, is this version 4.00.6304.00?

Fee

The question should be [red]Is it worth trying to do?[/red] not [blue] Can it be done?[/blue]

 

[xlbo]

If this is a specific ODBC issue, you be better off posting here:

forum706

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[SamDazz]

Willif:
I did rebuilt the ODBC connection but may be it could be resolved by unistalling the MDAC, so, is there any way we can reinstall the MDAC? Once MDAC is uninstalled, I can reinstall it and make a new ODBC connection to test.

XLBO:
Please read my post from 23 Aug 2007, and suggest what could be done.

Thanks