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 5 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply