Any reason CurrentDb.TableDefs(0).Connect returns empty sting in Access 2007?

  • Thread starter Thread starter GollyJer
  • Start date Start date
G

GollyJer

I use the function below to return the server or database name for various
uses.
However, now that we've switched to 2007 the line
CurrentDb.TableDefs(0).Connect
which used to return the connection string is returning an empty string.

Any ideas?

Thanks,
Jeremy


Function GetConnectionProperty(ServerOrDatabase As String) As String
' Returns name of connected server if "Server" passed in.
' Retuns name of connected Database if "Database" passed in.
Dim sConnectionString As String
Dim aConnectionPieces() As String
Dim sConnectionPiece As String
Dim i As Long

On Error Resume Next

sConnectionString = CurrentDb.TableDefs(0).Connect
aConnectionPieces = Split(sConnectionString, ";")

For i = LBound(aConnectionPieces) To UBound(aConnectionPieces)
sConnectionPiece = Mid$(aConnectionPieces(i), 1,
InStr(aConnectionPieces(i), "="))

If sConnectionPiece = "SERVER=" And UCase(ServerOrDatabase) = "SERVER"
Then
GetConnectionProperty = Mid$(aConnectionPieces(i), 8)
Exit For
End If

If sConnectionPiece = "DATABASE=" And UCase(ServerOrDatabase) =
"DATABASE" Then
GetConnectionProperty = Mid$(aConnectionPieces(i), 10)
Exit For
End If
Next i

End Function
 
Suggestions:
- TableDefs(0) might be a different (local) table?
Test it's Name.

- The database is not in a trusted location, so the code is not running:
Office Button | Access Options | Trust Center | Trust Center Settings

- The "Server' bit doesn't match.

This is what I use to get the data path for a specific table:
http://allenbrowne.com/ser-53code.html#GetDataPath
 
Allen,
You were right. TableDefs(0) was turning some sort of local system table
called MSysAccessStorage (which it didn't do in pre-2007 versions). 1, 2, 3,
etc. returned some other sys tables: MSysNavPaneGroups, MSysComplexColumns,
MSysAccessXML

I figured the real tables were now at the end instead of the beginning and
changed the line:
sConnectionString = CurrentDb.TableDefs(0).Connect
to
CurrentDb.TableDefs(CurrentDb.TableDefs.Count - 1).Connect
which looks a the last table instead of the first and everything works great
again.

I'm not sure what you meant by "Server" doesn't match though. Am I missing
something?
In immediate window ?GetConnectionProperty("Server") works great with this
function.

Thanks for the help,
Jeremy
 
Back
Top