Getting Backend Info From Access 2000 via ODBC

Ed Walsh
Ed Walsh used Ask the Experts™
on
If I have an ODBC connection to an access db.  what query can I issue that will give me a response to let me know what the backend is?  For example if I have an ODBC to SQL Server I can do a SELECT @@VERSION and get 'Microsoft SQL Server  2000 - 8.00.665 (Intel X86)   Jul 29 2002 15:02:32   Copyright (c) 1988-2000 Microsoft Corporation  Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 3)'.  I need to do the same in Access.   The program will not know beforehand what the ODBC connection is, ie. by name, but will need to determine what the backend is.  The program must be the one to discover not the user by going to control panel/odbc.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
If you design the table in code you can then check the connect property this will at worst tell you your DSN.

With the DSN you should then be able to do an ODBC API or Registry Call call to get the driver Info.

Cheers, Andrew
There is unfortunately not a single SQL statement that will produce version information across DBMS products. Can use the table Connect to determine the database type, then use a conditional statement to issue the correct command to the database.

function version(strVendor, strConnect)

' Valid connection string?

if strConnect=empty then
  exit function
else
  sp = InStr(strconnect, "ODBC;")
  If sp > 0 Then
    strconnect = Left(strconnect, sp - 1) & Right(strconnect, Len(strconnect) - (sp + 4))
  End If
end if

select case strvendor
case "MSSQL"
  sqltext="SELECT @@VERSION"
case "MySql"
  sqltext="SELECT Version()"
...
end select

Dim dbconn As New ADODB.Connection

with dbconn
  .Open strConnect
  set rs = .execute(sqltext)
  if not rs.eof then version=rs(0)
end with

dbconn.close: set dbconn = nothing

end function
mcallarse, the connect string doesn't tell you what ODBC driver you are using, it will tell you the Data Source Name (DSN) but that couls be pointing to anything eg

ODBC;DSN=Local Panrix;APP=Microsoft® Access;WSID=PANRIX;DATABASE=Northwind;Trusted_Connection=Yes;TABLE=dbo.Categories

I only know this is MS SQL Server as I know my DSN is using the SQL Server driver, if someone wanted to they could change the DSN.

Cheers, Andrew
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Was storing the DB type in the description field and getting it from there; didn't realize Connect didn't return extended properties.

All works to ewalsh's benefit though. Dug a little deeper, and here's the second pass, no SQL needed:

function version(strConnect)

' Valid connection string?

if strConnect=empty then
 exit function
else
 sp = InStr(strconnect, "ODBC;")
 If sp > 0 Then
   strconnect = Left(strconnect, sp - 1) & Right(strconnect, Len(strconnect) - (sp + 4))
 End If
end if

Dim dbconn As New ADODB.Connection

with dbconn
 .Open strConnect
' Debug.Print .Properties("dbms name")
 version = .Properties("dbms version")
end with

dbconn.close: set dbconn = nothing

end function
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Split: TextReport and mcallarse
Please leave comments here within the next seven days.
Answered: Archery
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

nexus
EE Cleanup Expert for Microsoft Access

***some of these are difficult to judge since the questioneer never gave input.
Nexus, how does Archery get a mention?

Cheers, Andrew
gah! i apologize. i thought i took care of these.

i built a excel file to help me with the spring cleaning. accidentally, it made a mistake (i fixed it) in copying 5 of these that also contained "archery". i thought i posted in all 5. i missed one.

nico already 'jumped' on me here: http://www.experts-exchange.com/Databases/MS_Access/Q_20404560.html. lol

sorry for any confusion... =)
per recommendation

SpideyMod
Community Support Moderator @Experts Exchange

TextReport points for you at:
http://www.experts-exchange.com/Databases/MS_Access/Q_20525569.html

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial