What database am I using?

[NevilleT]

I have an application that some people will use with SQL Server and others
with Access. There are a few differences I need to cater for with SQL Server
such as stripping off the dbo_ from the front of linked tables. Is there a
way to check a linked table to decide if it is Access or SQL Server?

 

[RoyVidar]

I have an application that some people will use with SQL Server and others
with Access. There are a few differences I need to cater for with SQL Server
such as stripping off the dbo_ from the front of linked tables. Is there a
way to check a linked table to decide if it is Access or SQL Server?

Yes, one way, would be using the MSysObjectsTable

SELECT Name,
Switch(
Type = 1, "Native table",
Type = 4, "ODBC linked table",
Type = 6, "Jet linked table")
FROM MSysObjects
WHERE Type In(1, 4, 6)

Also, I think if you loop the tabledefs collection and study the connect
property of each tabledef object, it should return the connection string
if it is linked, where you can investigate what type of database you're
using.

 

[Allen Browne]

Neat, Roy.

I was thinking of parsing it with Split() from the Connect property of the
TableDef.

 

[RoyVidar]

Neat, Roy.

Thanx - I like the Switch and Choose functions.

 

[NevilleT]

Thanks very much Roy and Allen (again). I modified what you suggested to do
the trick. Just for completeness and to help anyone else who has this issue,
the final code is:

Dim strSQL As String
Dim tbl As TableDef

' Used for SQL Server database which attaches a "dbo_" to the front of
table names.
strSQL = "SELECT * FROM MSysObjects WHERE Type = 4;" ' Type 4
= ODBC

If funRecordCount(strSQL) > 0 Then ' Check
if a record is found
For Each tbl In CurrentDb.TableDefs
If Len(tbl.Connect) > 0 Then
tbl.Name = Replace(tbl.Name, "dbo_", "")
End If
Next
Set tbl = Nothing
End If

funRecordCount is a generic function I use in the application

Public Function funRecordCount(strSQL As String) As Integer
Dim dbs As Database
Dim rst As Recordset

On Error GoTo Error_funRecordCount

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(strSQL, dbOpenSnapshot, dbSeeChanges)

' Find the number of records. First test records were found.
If (rst.EOF = True) And (rst.BOF = True) Then
funRecordCount = 0
Else
rst.MoveLast
funRecordCount = rst.RecordCount
End If

Exit_funRecordCount:
Set dbs = Nothing
Set rst = Nothing
Exit Function

Error_funRecordCount:
MsgBox "Error in funRecordCount " & Err.Number & " - " & Err.Description
GoTo Exit_funRecordCount

End Function