ODBC --Connection to 'XXXXX' failed.

Hi Experts
I have an Access 2003 database running from a mapped drive "Z:\database.mdb".

Three PC's have access to z:\database.mdb

The database has it's own tables as well as linking into some tables of SQL Server 2000.

I have setup System DSN to the SQL server on all the PC's.

Now the PC on which I created the database and linked all the tables can open the linked table just fine.  But when I try to open the linked table on any of the other PC's I get the error ODBC --Connection to 'XXXXX' failed.

But here's the twist, if I refresh a linked table (call it table1) on another PC using 'Linked Table Manager' in Access.  Then I can double click on table1 on that PC and open that table, but only on that pc.  If I go back to the original PC where the table was first linked it can no longer open the table.  The original PC can regain 'control' of the table by using 'Linked Table Manager' in Access and again refreshing the table.

So experts I believe that because all three are calling the same database.mdb the settings for the linked table are saved for one PC only.  Thus if another PC comes along they get the ODBC --Connection to 'XXXXX' failed error.  

Can any experts clarify this and provide a workaround.

Thanks

omfgwtflolbbqAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

therealmongooseCommented:
In my experience if you use a file dsn rather than a system dsn, the link will work on any pc.

Kind regards,

Mongoose
0
TextReportCommented:
In my experience you are better using DSN less connections. This eliminates errors on the DSN and you can control everything from within your application

"ODBC;DRIVER={SQL Server};SERVER=PackardBell;APP=My Application;WSID=COMPUTERNAME;DATABASE=Northwind;USER=sa;PWD="

When I use this string I replace COMPUTERNAME with the name of the computer.

Finally do not share the front end let each user have a copy locally and if some of the access tables are shared either move the to SQL Server or have them in a DATA.MDB file on Z:

Cheers, Andrew
0
Chris BRetiredCommented:
therealmongoose is entirely correct, that is exactly what file dsn is for. Generally though, system dsn will function provided the ODBC setup is absolutely identical on each pc (as it is when you use file dsn, which you know because you use the same file).

Chris B
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Chris BRetiredCommented:
p.s. Using Z: isn't such a good idea, as if the drive isn't mapped it won't work. Using an absolute path avoids the need for mapping - \\server\directory\database.mdb

Chris B
0
TextReportCommented:
Chris its Head You Win Tails You Loose with whether you use drive mappings or universal naming convention (UNC), drive mappings are good when they are set by your login script and easy to change should the database be moved, UNC's keep you as the developer in control but if they decommission the server then you will have to modify your database.

Cheers, Andrew

PS I normally use the UNC's but I have had problems with one client
0
Chris BRetiredCommented:
My organization has used G for department share and H fror property share for the last 100 years or so. IT department decided recently to change them to X and Y....

Chris B
0
omfgwtflolbbqAuthor Commented:
Hey Guys
Thanks for the input.  

So basically going from system DSN to file DSN should eliminate the errors I am seeing?

Can I please ask how I setup a file DSN?

Thanks
0
TextReportCommented:
I wouldn't bother with eiter File or System based DSN, use the DSN Less option and you don't need to configure it.
Obviously you need things installed.
Cheers, Andrew
0
therealmongooseCommented:
You can set up a file DSN through control panel> Administrative Tools> Data Sources (odbc). One of the  tabs is File DSN > hit the add button and away you go....


you can then link your table using (in access) using File>Get External Data> Link Table>
then select odbc data sources....
0
omfgwtflolbbqAuthor Commented:
Hey Guys
I'm a real n00b:

textreport: where do I setup DNS Less option

therealmongoose: I get as far as you suggest but which file do I browse for?  The database.mdb or another file that I have to create.  If so what do I put in that file?

Thanks
0
TextReportCommented:
You need code to do the attachements, I normally store the connection strings in tables. I have attached my default code.
Cheers, Andrew

Option Compare Database
Option Explicit
 
Function AttachAllTablesAndViews(pstrODBC As String, Optional pbooSilent As Boolean) As Boolean
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim rst As DAO.Recordset
Dim td As DAO.TableDef
Dim booSilent As Boolean
 
    If IsMissing(pbooSilent) Then
       booSilent = False
    Else
       booSilent = pbooSilent
    End If
    
    Set db = CurrentDb()
    Set qd = db.CreateQueryDef("", "SELECT [name] FROM sysobjects WHERE xtype IN ('U','V') ORDER BY [name]")
    qd.Connect = pstrODBC
    
    Set rst = qd.OpenRecordset()
    Do While Not rst.EOF
       
       Set td = db.CreateTableDef(rst![Name])
       If InStr(pstrODBC, ";PWD=") > 0 Then
          td.Attributes = dbAttachSavePWD
       End If
       td.Connect = pstrODBC
       td.SourceTableName = rst![Name]
       
       db.TableDefs.Append td
       
       rst.MoveNext
    Loop
    rst.Close
    
    Application.RefreshDatabaseWindow
 
    If Not pbooSilent Then
       MsgBox "All tables and Views have been attached"
    End If
    
    AttachAllTablesAndViews = True
    
End Function
 
Function fbooDropAttachedTables(Optional pbooSilent As Boolean) As Boolean
Dim ws As Workspace
Dim db As Database
Dim td As TableDef
Dim cnt As Long
Dim booSilent As Boolean
Dim strSQL As String
Dim Qty As Long
 
    If IsMissing(pbooSilent) Then
       booSilent = False
    Else
       booSilent = pbooSilent
    End If
    
    Set db = CurrentDb()
    
    Qty = db.TableDefs.Count - 1
    For cnt = db.TableDefs.Count - 1 To 0 Step -1
        Set td = db.TableDefs(cnt)
        If td.Connect <> "" Then
           db.TableDefs.Delete td.Name
        End If
    Next cnt
    
    Application.RefreshDatabaseWindow
    
    If Not pbooSilent Then
       MsgBox "All attached tables have been dropped", vbInformation, "Drop Attach Tables"
    End If
    
    fbooDropAttachedTables = True
    
End Function
 
Sub Test_AttachAllTablesAndViews()
 
    fbooDropAttachedTables True
    AttachAllTablesAndViews "ODBC;DRIVER={SQL Server};SERVER=PackardBell;APP=My Application;WSID=COMPUTERNAME;DATABASE=Northwind;USER=sa;PWD=", True
    MsgBox "Test Attached Tables and Views Done"
End Sub

Open in new window

0
therealmongooseCommented:
Sorry omfgwtflolbbq, should have given you a bot more detail - once you get to the file dsn tab, press the add button, scroll through the list and choose sqlserver
(assuming your source database is on an sql server...). In the "type the name of..." text box, just enter a name that is meaningful to you - "mydatasource" (no need to use the browse button unless the data source is already set up...)
Hit next

Hit Finish
enter a meaningful description
You will need to enter the full path of the server :

eg \\md_projs01\
- (you can get this by going into the design view of the linked table in access and choose view properities for the table - server details will be listed in the connection )

You may need to get some support from your is team if you have one as you may need to get permissions to access the server and database.

The next screen asks if you want to use windows authentication (whoever is logged into the local pc) or use a specific user name and password to access the database (probably an admin set of details - again you may find this in the connection string in the properties of the linked table....)

Good luck...

Textreports post is also a very workable method if you are familiar with vba...


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
omfgwtflolbbqAuthor Commented:
Yay.  I finally got time to try it out today.
therealmongoose: I used your method of File DSN to get it working.  Now all my users can have access to the SQL server linked tables.  I put the File DSN on Z:\ .

TextReport: I'd like to pretend to know VBA but I can't so I won't.  Your method probably works but I don't know what bits to change and where to even put this code(form Load Event??).  But I gave you some points for your contribution.

Thanks all.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.