October 25, 2010 at 4:54 am
I have been working now on an Access 2007 database which links to SQL Server 2008 to import data for a client.
I will say now that i am running Access 2007 up to date with SP2 on a fully updated Windows 7 64-bit :blink:
What is happening is that i have a series of linked tables.
I originally created a File DSN and used the link table manager to re-link the tables to this, at first it worked, data could be read from SQL via Access, then it broke with the ODBC Call Failed command - nothing had changed (seriously, nothing).
Now when I try to re-link the tables I tick the "Always prompt for new location", select the linked table to update and click "OK", the error comes up immediately with no delay.
If I delete the linked table and recreate the link then it works, but as soon as I try to relink it, it breaks.
The file DSN is so that I can ship the prebuilt DSN connection to the client with instructions on what to do if necessary.
I did try the ODBC manager to create a Machine DSN - deleting the linked table, re-linking it and seconds later it broke again.
I even deleted the Machine DSN and ran the 32-bit ODBC manager and created it this way, but no avail.
Now, whilst i am working on the database I am constantly having to delete and recreate 10 linked tables every few minutes and not only is it slowing me down, it is becoming highly irritating. I can't ship this out to the client (who only run 64-bit Windows) unless i can get this fixed, and whilst they have been waiting patiently for 2 months, they are now running out of patience.
I can't downgrade to 32-bit (not our company policy) and i certainly can't ask the client to do the same.
I have been Googling this for days now with no real results and I'm now going google-eyed looking at the screen :hehe:
Has anyone found a solution (please answer soon as I've got so many fingers and limbs crossed for luck that I think my blood circulation may soon stop).
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
November 6, 2010 at 5:53 am
Alt F11 to open the VBA editor, in the immediate window put:
? CurrentDb.Tabledefs ("dbo_Yourtable").Connect <ENTER>
this will display the connection information for the table "dbo_Yourtable" in the immediate window (use the name as it appears in the Access navigator, not as in SQL Server).
John
November 6, 2010 at 1:56 pm
Another thought - I can't really comment on file data sources, but I have plenty of experience with user (stored in profile) and system (stored in the windows registry under HKLM).
I am assuming that although you are running 64 bit windows, you are running 32 bit MS Office, and your client is doing the same - please ignore this next suggestion if it is not the case.
When a 32 bit process (such as MS Access) is running on an x64 operating system, you need to ensure that the ODBC connections are created using the 32 bit version of the ODBCAD32.EXE program. Using teh control panel or start -> run -> odbcad32 will run the one that creates them under the 64 bit registry keys; for those under 32 bit you need to use
start -> run -> c:\windows\SysWOW64\odbcad32.exe
If not, Access won't be able to access them.
Worth trying this.
November 8, 2010 at 4:03 am
Many thanks for your replies.
I had already tried creating ODBC connections using the 32-bit application (you are correct, I am running 64-bit).
When I finally managed to squeeze a 32-bit client from the company and the access database was moved to it, it has been connecting with no problems.
The issue with 64-bit continues, whether we create a DSN file or set up an ODBC using the 32-bit application, it frequently simply refuses to connect and refuses to even allow us to try and re-make the connection until we delete the linked tables and recreate them. I've done some hunting around the web and at least have discovered that I am not alone, it appears that Access seriously dislikes 64-bit systems.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
November 9, 2010 at 1:12 am
OK What you need is a bit of DAO code to do the job. I'm working in 32 bit 2010 on W7 x64 against SQL 2008 R2 on windows server 2008 R2 and this works for us.
Public Sub LinkTableDAO(TableName As String, _
ConnectionString As String)
Dim db As DAO.Database
Dim tdf As DAO.TableDef
On Error Resume Next
Set db = CurrentDb
Set tdf = db.TableDefs(TableName)
If Err.Number = 0 Then
' Found an existing tabledef.
db.TableDefs.Delete TableName
db.TableDefs.Refresh
Else
' No existing tabledef.
' Ignore error and reset.
Err.Clear
End If
On Error GoTo HandleErr
' Create a new TableDef object
Set tdf = db.CreateTableDef(TableName)
' Set the Connect and SourceTableName
' properties to establish the link
With tdf
.Connect = ConnectionString
.SourceTableName = "dbo." & TableName
End With
' Append to the database's TableDefs collection
db.TableDefs.Append tdf
db.TableDefs.Refresh
ExitHere:
Exit Sub
HandleErr:
Select Case Err.Number
Case Else
MsgBox "Error: " & Err.Number & " " _
& Err.Description, , "Link Tables DAO"
End Select
Resume ExitHere
End Sub
We build our connection strings programmatically as well
Public Function SetConnectionODBC()
SetConnectionODBC = "ODBC;Driver=SQL Server;" & _
"SERVER=" & DServer & ";" & _
"DATABASE=" & DYear & ";" & _
"Trusted_Connection=Yes"
End Function
We have extra code to create indexes on views so that we can update them. We have a list of tables \ views stored in a local table and have a wrapper function which reads the table name from this table and then calls LinkTableDAO. You will notice that doing it this way we avoid the schema prefix in front of the table name although we assume it's dbo in the code. If the schema prefix varies, you may need an extra parameter.
You will also notice that when we re-link we delete and recreate the table connection.
Hope this helps.
November 9, 2010 at 1:24 am
Thanks, I'll give it a try
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply