SQL to return edition, product version for MS Access?

[Guest]

I am writing a data abstraciton layer for a web application that I would like
to be able to support multiple database types with (i.e. MS SQL Server,
Access, etc.) Part of this is being able to query for the edition and
version for the database.

The following query works with MS SQL Server:

SELECT SERVERPROPERTY('edition') AS edition,
SERVERPROPERTY('productversion') as version

Is there an equivalent that will work with MS Access?

Thanks!

 

[Guest]

Hi.

Is there an equivalent that will work with MS Access?

No. Jet has no system catalog or data dictionary for you to query. You
would need the Access library to retrieve the Access version, and there's no
such thing as an "edition" to describe Access programmatically. However,
there is a "build number" for each version of Access, which changes depending
upon which service pack has been installed. In VBA, the code to get the
Access version number is the following:

Access.Application.SysCmd(acSysCmdAccessVer)

.. . . which returns a Single data type. To get the build number:

Access.Application.SysCmd(715)

.. . . which returns a Long data type.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact info.

 

[Guest]

Hmm. That's a shame.

Any chance there's any Access-specific SQL that I could run (i.e. something
that would fail on everything but access)? Wrapped in a try/catch block I
could determine the dbtype by what query throws an error.

FWIW, VBA isn't an option, this is a web app that's written in ColdFusion.

Thanks again,

Pete

 

['69 Camaro]

Hi, Pete.

Any chance there's any Access-specific SQL that I could run (i.e.
something
that would fail on everything but access)?

If you're interested in foreign key constraints, database object names and
types, object creation dates, object modification dates, import/export
specifications, and queries, then you could use SQL to retrieve this
information from Jet's system tables. But Jet's system tables don't contain
very many of the items you'd normally find in a server database engine's
system tables, so if you are looking for more than what I just listed,
you'll be disappointed.

FWIW, VBA isn't an option, this is a web app that's written in ColdFusion.

You hadn't mentioned which Web scripting language you were using for your
Web application. Although it's not recommended to do so, a few of them allow
instantiation of an Access application object, which would have enabled you
to use that code. By giving you the code, I figured that you'd know at
glance whether or not you could pull it off.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Douglas J. Steele]

You hadn't mentioned which Web scripting language you were using for your
Web application. Although it's not recommended to do so, a few of them
allow instantiation of an Access application object, which would have
enabled you to use that code.

Of course, that requires that Access be installed on the server, which isn't
always the case.

 

['69 Camaro]

Hi, Doug.

Of course, that requires that Access be installed on the server, which
isn't always the case.

Of course, any software developer who understands the meaning of
"instantiation of an Access application object" knows this requires the
Access library, which requires that Access be installed.

But of course, there are those who blindly copy and paste code from their
Internet Easter Egg hunts, without fully understanding what the code does or
how it does it, so thanks for alerting them as to what to fix when this code
won't work for them in their Web applications that try to automate Access.
And they can go on to build more Web applications that automate Office
applications and cause problems with the Web server.

In other words, I think these Internet Easter Egg hunters do more harm than
good, so I'd rather not hand out flashlights which enable them to make their
way through the dark to practice their mischief. So if it appears that I'm
being stingy with flashlights, you can be sure that those who generously
supply those flashlights will provoke a response from me. Of course. ;-)

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.