Some tables not available via ODBC after upgrade

[Galaxiom]

I have a database originally running in SQL Server Express 2000 on Windows Server 2003.

The database was backed up and then restored to a new Windows Server 2003 machine running SQL Server Express 2005.

The database on the new machine is all present when viewed in Management Studio. I had an ODBC connection to the old server and never experienced any problems.

However, even though I can connect to the new SQL Server database from Access, some of the tables are not showing on the Import External Data list of tables. The tables that are shown connect perfectly.

I have tried a new Access database, new ODBC connection but still the tables will not appear. I contacted the database designers who oversaw the upgrade and they confirmned they simply added several new tables but had made no changes to the database that would have affected the missing tables.

The missing tables all share a similar naming format and structure.

I have trawled over all the permission and properties of everything I can find but to no avail.

Any suggestions are most welcome.

 

[Banana]

What if you tried it in code? It may error out but hopefully it may give you a hint.

Something like that: (adding one table is good enough for the testing/troubleshooting)

Private Sub AddOneTable()

Const DisplayName As String = "<The name as shown in Access>"
Const TableName As String = "<Your missing table name as stored on SQL Server>"
Const ConnectStr As String = "<Your DSN name or full connection string>"

Dim tdf As DAO.TableDef

Set tdf = CurrentDb.CreateTableDef(DisplayName)
With tdf
   .Connect = ConnectStr
   .SourceTableName = TableName
End With
CurrentDb.TableDefs.Append tdf

Set tdf = Nothing

End Sub

(untested)

See what happens.

 

[Galaxiom]

Thanks Banana. Your code worked straight up.

It can create a new table using one of the tables that is visible in the import window.
However using the missing table throws an error 3011. (Access cannot find the object 'the missing table name').The name is definitely correct and the connection obviously is OK since it can connect to another table.

Debug highlights this line:
CurrentDb.TableDefs.Append tdf

I originally used a pass through query and went looking for the table in the Import Window when it failed. The query worked fine on the old server.

I am bewildered that the tables are there in SQL Server but not appearing for Access or VBA. There must be some property of those tables in SQL Server 2005 that prevents their connection. I have my login ID set as owner and admin.

The original Web based application that uses the database from the same server is working fine.

 

[Banana]

Curious!

Two more possibilities I would want to investigate.

1) What if you used a different driver? I doubt it is really the issue as any driver issue would be all or nothing and I would think it's more likely permissions but I'd like to rule it out. An example of different driver is using SQL Native Client, SQL Native Client 10.0 or SQL Server.

2) Can you use a different login account? Just to rule out any funkiness owing to client profile.

 

[Galaxiom]

Same results with the SQL Native Client connection.

The Server is set up to allow Domain Admins so it is hard to test other logins because this one is happy to let me in with full rights anyway.

 

[JGT]

I would do this dumb test. In the SQL Server: 1. Rename one of the problem tables 2. Create a new table with the former name. 3. Copy all data from the original table to the new table
See if you can access the new table through ACCESS.

 

[Galaxiom]

Bizarre. I created a new table in the database and it didn't show up on the client.

So I created a new database with one table in it and made a new ODBC file DSN with that as the default database. I could connect to it as normal.

Suddenly the test table in the original database connection on a different DSN started working properly too. The others also appeared. Deleted the test database and now everything is back to normal.

A painful day that has left me none the wiser about what the hell was wrong.

Thanks for the suggetions.