[AccessD] Database Conversion in VBA

Marty (and all),

  A did a sample database a while back to find all MDB's in a path and build
a table with the result.  Anyone that wants a copy can e-mail me off-line.

Jim Dettman
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of MartyConnelly
Sent: Friday, July 23, 2004 9:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Database Conversion in VBA


Well here is some rough code that will scan through directories and sub
directories.
to determine an mdb's version and print out the Access Version and Jet
Version numbers.
You will have to run this from Access 2002 or 2003, otherwise higher
versions will not be recognized.
This code will not be able to get at mdb's that are  read only versions
or those opened exclusively.

Sub VersionTest(strpath As String)
'VersionTest "C:\Access files\Images\Picture2002.mdb"
On Error GoTo ErrHandler
Dim db As Database
Dim i As Long
Set db = DBEngine.OpenDatabase(strpath)
Debug.Print strpath & " - " & db.Version & " - " &
db.Properties("AccessVersion")
'For i = 0 To 8
'  Debug.Print db.Properties(i).Name & "-" & db.Properties(i).Value
'Next
Exit Sub
ErrHandler:
' most likely 3051 read only databases
MsgBox Err.Number & " - " & Err.Description & vbCrLf & strpath
End Sub

Sub testfind()
  FindSub "C:\Access files\", "*mdb"
End Sub
' FindSub() ermittelt alle Dateien mit der im Commondialog
' ausgewählten Dateiendung bis zur untersten Ebene ab dem gewählten
'Verzeichnis.
' Hierzu wird ein rekursiver Aufruf der Routine FindSub() eingesetzt.
' FindSub() determines all files with in the Commondialog the
' selected file dung up to the lowest level starting from the selected
'Verzeichnis. ' For this a recursive call of the routine FindSub() is used.
Sub FindSub(strStart As String, strFindWhat As String)
Dim arrFindDir() As String
Dim strFind As String
Dim i As Integer

ChDrive (Left(strStart, 3))
ChDir (strStart)

Call DirSub(strFindWhat, strStart)

strFind = Dir("*.*", vbDirectory)
i = 0

'ermittelt alle vorhandenen Dateien und Verzeichnisse im aktuellen
'Verzeichnis
'ermittelt all existing files and listings in the current 'Verzeichnis
Do Until strFind = ""
ReDim Preserve arrFindDir(i)
arrFindDir(i) = strFind
i = i + 1
strFind = Dir()
Loop

For i = 0 To UBound(arrFindDir)

' If... ermittelt ob es sich um ein Verzeichnis handelt
' und verzweigt dann rekursiv in dieses Verzeichnis
If Dir(arrFindDir(i), vbNormal) = "" And Left(arrFindDir(i), 1) <> "." Then

' Rekursiver Aufruf von FindSub
Call FindSub(strStart & arrFindDir(i) & "\", strFindWhat)
ChDir (strStart)
End If
Next

End Sub

' ermittelt alle gesuchten Dateien im momentan aktuellen Verzeichnis
' und gibt die gefundenen Dateien mit Pfad in eine Listenfeld (lstFiles)aus.
' determines all looked for files in the momentarily current listing
' and gives the found files with path in list field (lstFiles)aus.
Sub DirSub(strFindWhat, strStart)

Dim strFindfile As String

strFindfile = Dir(strFindWhat, vbNormal)

Do While strFindfile <> ""

VersionTest strStart & strFindfile
'.lstFiles.AddItem strStart & strFindfile
strFindfile = Dir()
Loop

End Sub




Charlotte Foust wrote:

>We need to examine a datafile in code and convert it if it is in 2002
>format.  Our users don't see the menu to do this, and we couldn't safely
>leave it to them anyhow.  There are no constants except
>acCmdConvertDatabase, and the only code I've seen used SendKeys to run
>that in A2k.  Since 2002 has 3 formats for conversion and we wouldn't be
>running this in the current database, it becomes problematic.
>
>Charlotte Foust
>
>
>-----Original Message-----
>From: Arthur Fuller [mailto:artful at rogers.com]
>Sent: Thursday, July 22, 2004 2:03 PM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Database Conversion in VBA
>
>
>Is there something wrong with doing it in the menu? Do you need to do it
>programmatically? If so, there must be constants that you can pass in to
>DoCmd that will achieve same. Never had the need to do it yet but what's
>wrong with the menu options themselves?
>
>Just asking
>
>Arthur
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Charlotte
>Foust
>Sent: Thursday, July 22, 2004 4:59 PM
>To: AccessD at databaseadvisors.com
>Subject: [AccessD] Database Conversion in VBA
>
>
>Has anyone succeeded in converting a database from 2002 to 2000 format
>in code?  No matter what I try, I only trigger the conversion dialog
>without successfully passing in the arguments it needs.  What I'm
>looking for is 2002 code that will specify a database file and convert
>that file to 2000 format.
>
>Charlotte Foust
>
>

--
Marty Connelly
Victoria, B.C.
Canada



--
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com