Help getting ODCB working on Wiin7 client to SQLEXPRESS on server

I have found that using the 64 ODCB is problematic--try the 32 bit version; when you get to the end of the setup and try "test connection" is it connecting successfully? And what user are you using when creating this ODCB, is it the same user you used when you created your database in SQL Express; are you using sa or Windows user authentication to logon to SQL EXpress?

Are you sure that your SQL database is listening on the standard port, 1433?  If not, it's a bugger to get ODBC to use another port.  

You would think that you'd set up the port using the IP address, colon, and then the port number, but that's not the case.  You must use a comma with no space.  Learned this the hard way recently!

Double check the firewall on the new server as it may be blocking 1433 or perhaps another port if it has been modified.

Also, depending on the requirements, you may need to set this up as a file, system, or user DSN.  User DSN is trickier in a multi-user scenario.  I ended up injecting the user DSN info into the user registry (profile) via a batch file.

So far as deleting extraneous ODBC configs, do a search for the odbc.ini folder in the registry.  You will likely find multiple entries under HKLM\Software and/or HKCU and/or even HKU.  Cautiously delete any that you don't feel should be retained.

If the workstation is 64-bit, then you must use the odbcad32.exe file from the SYSWOW64 folder, not the standard ODBC setup file from the Windows\system32 folder.  If the workstation is 32-bit, then you must use the standard ODBC setup file which can be run by going to Control Panel\Administrative Tools\Data Sources (ODBC). If you don't use the correct odbcad32.exe setup program, then the connection will not work.

I advise logging on as a local administrator on the workstation and then set up the ODBC connection as a System DSN rather than a user DSN.  This guarantees that once you have it set up and working, the user can't change anything.

During the setup of the ODBC connection you should be able to test the connection to the server.  If the setup doesn't complete successfully, then the workstation cannot contact the server (i.e., the port is wrong or there's a firewall issues or something of that nature as suggested by joharder).  If the setup completes successfully using the correct ODBC setup, correct port, etc., then the problem is most likely a security issue, i.e., the user doesn't have a login for the SQL database or doesn't have appropriate rights to the database you're trying to use.

Also - another thought that just occurred to me is that you may not have the most up-to-date SQL client software on the workstation. If you're using 2008 or later, you may want to download and install the SQL Native Client 10, which is part of the SQL 2008 Feature Pack:

http://www.microsoft.com/en-us/download/details.aspx?id=16978

Thank you all for trying to help me...   This  answers a couple of above questions.

The new Win7 PC is 64 bit hardware.  All other client PCs are also 64 Bit.

FYI.  "Frank" is an Admin on ALL Client PCs, the SERVER, and fully privileged in SQLEXPRES.

SQLEXPRESS on the server is Windows Authentication aka "Trusted".

I have 3 Client PC's:  One is an older Win7, and 2 Win Vista that all run my Server 2008 R2 accde AP just fine.

Therefore... I'm thinking this is not a server, port, or SQLEXPRESS issue.  And, the problem is in the new Win7 PC and my lack of knowledge to properly set up the OCDB.  

I ran SYSWOW64 odbccad32.exe  (the “exe”) from a shortcut I placed on my desktop.  I assume this is the correct 32 bit version Access 2007 requires.

I ran exe from an Admin account on the PC, who is only a standard user on the Server.  I think I also did it from the “Frank” account as well, as I was flailing about trying things.  I need to settle down and quit flailing (:-)

I will go back to office and make sure I am using the “Frank” account.  If that fails I will install the Native Client 10 suggestion.  If done…  will the “exe” now show SQL NC 10 as an option to choose?

Also... as half the options in the "exe' are a mystery to me  I pretty much accept all the defaults...

Once you install the native client version 10, you'll see a second SQL option when you create a new ODBC connection, which you'll want to select:  SQL Server Native Client 10.

Ok.. I installed the SQL Server Native Client 10, built the "SYSTEM" OCDB as advised.  It connected.  Things are working better than they did before, but I have a strange issue or two.

Repeating myself:  Frank and Jim are administrators on the new PC,
Frank is an administrator on the Server as well, and SQLEXPRESS handles the Group of users.,
Jim is one of 5 users in that Group on the Server along with all the other users on the other old PCs and they are all working fine with accde AP from the Server on their PCs.

Frank now works Fine on new PC

Jim is not working Fine on new PC because the AP give SQLEXPRESS error in an "info/reply popup"  saying user (Jim) is a "Guest" not having permission.  It allow entering Frank's credentials in that "info/reply popup" but rejects Frank too.    That rejection of Frank seems impossible because I am an Admin everywhere possible including SQLEXPRESS....

Somehow Jim is not being qualified as being a member of the Group (where all other PC users in that Group are working Fine).   SQLEXPRESS thinks (or has been told) Jim is a Guest. Where is Jim losing his identity?  When new PC is talking to the Server?  or when Server is talking to SQLEXPRESS - ???  

I considered that Jim's account on the Server had an error, so I deleted his account and created another Jim account and put Jim back in the Group...  but that did not fix the problem.

Can you come up with ideas?    Is there a particular Account or Accounts that MUST be included on the Server share in the new PC I may have overlooked for Jim?   I have included SYSTEM, NETWORK, NETWORK SRVICE FRANK and JIM but not sure what is really needed on that server share.  

Thanks in advance for any thoughts or ideas....

I checked AP to find that I early on attach some SQL tables DSN less.  I need to go to office tomorrow to see if User Name and Password are being used properly...  could be a bug in there....  if not, may explains problem...  will update this thread tomorrow.
Thanks....  Frank

I don't think the problem is NTFS permissions, it sounds to me as though it's a problem with the SQL permissions.  Is the group that you're putting these users into given permissions to the database in SQL?  In other words, the group should be added to the Security in SQL with public permissions to the database that you want them to use.

Good question....  recall there are other PC users in that same server Group and they are working fine.  The Group has permissions in SQL (not PC users).   This problem (I think) is either the PC itself perhaps (and unlikely) NTFS permission related, or my AP is screwing up with this new user...   I'll know more later... I have some ideas to test in the AP itself.

On the new PC the my Adminstrator account is working fine with the Server accde.   Recall that what fixed this one was creating that System OCDB,

The other new PC administrator "Jim" fails.    Jim is in the same server Group with all other PC users that work fine.

On Jim, using msAccess 2007 I resorted to loading the ACCDB from the server and it loaded  and I found in the Navigator pane it looked like all my tables were there, but when clicked I was advised it needed SPN or credential and I tried, but that did not clear the issue (not sure I had SPN info correct).  So, I tried the "External tab"  "more" option to mess with its OCDB feature...  I found the System OCDB mentioned above, but it ended up prompting to provide SPN or credentials.  So, I even tried creating a new OCDB but it would not connect either.

For now I have run out of ideas.  It is all so very close to working, but no cigar.
Any thoughts or ideas most appreciated.

I found older Vista PC and set up Jim.  Like on Win 7 PC when AP tries to link in a table the error is:
Connection Failed:
Sql State '2800'
SQL Server Error 18456
[Microsoft][ODCB SQL Server Driver][SQL Server][Login Failed for user 'CORP\Guest'
ODCB -- Connection to SQL Server TED\SQLExpress failed code 3151

Somewhere Jim was Admin on his PC, a member of a Group on TED, but ended up at SQLExpress thinking him as Guest.

Other users in the Server Group have no problem running the AP from their PCs, but not Jim.

BTW...  Jim works on a similar 2008 server at my home using the above Vista machine.   Thought when I took PC to office it could not fail....   but it fails at the office.

FYI I also disconnected existing Shares while in office, and reconnected anew at Office , but that didn't help.

I am out of ideas....   I really need help...  Thanks in advance...

Note & FWIW:  I made sure all PC users had exactly the same permissions on their PC's server share.   Did not help Jim on his Vista machine whereas on that same machine user "Joe" works just fine.   Wouldn't that definately point to a server issue?

I insured Native Client 10 ODCB installed and AP worked with PC primary User.
Swithed to Jim hoping above fixed problem... but AP failed.

On Jim PC I tried setting up (again) a new ODCB and was not able to connect.   Tried both Authentications Options (primary User, and Jim), and was always rejected as "user not authorized"   Even tried the ODCB created above by primary user....  did not work.

Does each user require their own ODCB?  Is one ODCB supposed to work for all users?
How does invoking msaccess.exe to run an AP know which ODCB to use?

Thanks....  I apologize but do not know what a .reg file is...  purpose?  where does it get created  and gets placed where I tell it?  I will research to try to understand.

As for login credentials...  I have tried using my account (Admin everywhere) but the ODCB would not connect.

In next day or two I will try creating the DSN under my account and save it as a USER DSN....  
I did try to create the System DSN under the user account, but could not connect.  I will try the User option.

Thanks for trying to help me.

I have no solution....  Points awarded to those who tried to help.