Determining the file format for attached tables

[Margaret Bartley]

I have several hundred databases containing a table that has similiar but
not identical information to all the other like-named tables..

These were back-up tables over the past several years, and it has come to my
attention that there may have been some instances were data was overwritten,
so I'm writing code to link to each database in turn, attach the "books"
table, and do some crude totals and counts and write the result to a log
table.

Along with the database CreateDate and Modify Date, I'd also like to include
the database version for each table. Some are Access97, some are Access
2000 formats, even though I'm using Access 2003. There may be Access 2003
format tables, as well.

Is there any way to look at the tabledef properties and know what version of
Access format it is?

I'd like to not have to create a database object for each of the files, I
suspect that will slow things down a lot. But if I have to, I will.

I was just hoping that maybe there were some properties or values in the
tabledef that were unique to each version of the database, since I'm using
the tabledef anyway for other things.

 

[dymondjack]

'Return a string according to the current access version
Public Function GetAccessVersion() As String
Dim sVersion As String
sVersion = SysCmd(acSysCmdAccessVer)
Select Case sVersion
Case "8.0"
GetAccessVersion = "97"
Case "9.0"
GetAccessVersion = "2000"
Case "10.0"
GetAccessVersion = "2002"
Case "11.0"
GetAccessVersion = "2003"
Case "12.0"
GetAccessVersion = "2007"
Case Else
GetAccessVersion = "Future Version: " & sVersion
End Select
End Function


This will get a version of access, though I wouldn't know how to apply this
to a different db (I've never worked with more than one db at a time, but I'm
assuming that you could create a connection and somehow pull the info from a
separate file).


--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Margaret Bartley]

Doesn't this just get the version of my currentdb(), not the version of the
attached table?

My VBA code is in an Access 2003 db, but it's looping through directories
and subdirectories, pulling out every "Books" table it finds. I'd like to
know what version of Access the linked table is.

The only way I can think of is maybe there are some properties unique to
each version that will allow me to differentiate between them when examining
the table defiinition.

 

[dymondjack]

Doesn't this just get the version of my currentdb(),

It does, when used in your current db. I had thought that it might be
possible to somehow run this code 'remotely', as I know it's possible to
manipulate one database from another database. But, I've never done that, so
can't even begin to explain how. I thought maybe if you knew that you could
put the two together.

On a different note, I have some code at my house that loops through the
properties of any given file. I'm not sure, but you may be able to extract
the version from there (which is a function that would be run in your current
db, pointing to any file of your choice). That may work, but I won't be able
to post the code for a few more hours.

It would make sense that you could get this information by creating a
connection to the remote database, and then examining it's properties. I am
positive this can be done. Something like this

Dim dbs as Database
Dim sVersion as string
Set dbs = "C:\yourfolder\yourdb.mdb"
sVersion = dbs.Properties("Version")

I have no idea if this code is correct (probably not, this is the most I've
done with it), but maybe someone else can elaborate further. I don't think
it's all that far off.

goodluck


--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill

 

[Stuart McCall]

I have several hundred databases containing a table that has similiar but
not identical information to all the other like-named tables..

These were back-up tables over the past several years, and it has come to
my attention that there may have been some instances were data was
overwritten, so I'm writing code to link to each database in turn, attach
the "books" table, and do some crude totals and counts and write the
result to a log table.

Along with the database CreateDate and Modify Date, I'd also like to
include the database version for each table. Some are Access97, some are
Access 2000 formats, even though I'm using Access 2003. There may be
Access 2003 format tables, as well.

Is there any way to look at the tabledef properties and know what version
of Access format it is?

I'd like to not have to create a database object for each of the files, I
suspect that will slow things down a lot. But if I have to, I will.

I was just hoping that maybe there were some properties or values in the
tabledef that were unique to each version of the database, since I'm using
the tabledef anyway for other things.

See if this helps:

http://www.smccall.demon.co.uk/DAO.htm#AccessVersion

 

[Margaret Bartley]

Like the previous suggestion, this requires that I actually open the remote
database,
which I was hoping to avoid, as there are nearly 500 databases I'm going
through and that
could add a lot of time to this project.

But I've been through the table properties, and the field properties, and
they look the same,
so I probably will have to use the database file version info itself.

I'm not sure whether it will be faster to open the database and look at the
version, or to create a db object, and look at it's version. Maybe I'll try
it both ways, and see!

 

[dymondjack]

I did find the code that checks the fileproperties but it did not return any
version specific property.

I think your best bet may be setting a db object and pulling the property
from there.
--
Jack Leach
www.tristatemachine.com

- "Success is the ability to go from one failure to another with no loss of
enthusiasm." - Sir Winston Churchill