Converting from Access 97 to 2k3

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello, I have looked up and down the forum but I can't find a thread that
helps me. My company have hundreds of DB created on Access 97, but guess
what they just desided to upgrade to 2k3. Ok, the easy way to go and do it
is opening one by one all the databases but in reality that is not an option
to me. So I was searching for tools to do this convertion, will doing my
search I found Q304318 in the microsoft support. It look very simple just
create a Module paste the code and run. I really try doing it the way its
explain there but i'm getting pretty frustrated with it and nothing is
happening I can't even get into running it!

I would like to ask for the help of all of you in this situation. Maybe
directing my with more detail instructions on how to do it or helping find a
tool already created that will do the convertions. I'm trying to solve this
problem as soon as I can so i have more time testing and correnting the
issues that are going to appear.

Thanks for any help any one can provide me.

Luis
 
Thanks for responding so soon David. I have never heard of access 2004, can
you provide me with a bit more info? Also, I was wondering what i will be
looking for on the link you provided.

Thanks again David,

Luis
 
Thanks for responding so soon David. I have never heard of access 2004, can
you provide me with a bit more info? Also, I was wondering what i will be
looking for on the link you provided.
Click to expand...

"David" isn't who he pretends to be. He's a spammer trying to draw
traffic to an illegal warez site. Ignore him, and DON'T download
anything from his website.

John W. Vinson[MVP]
 
You have a LOT of DBs.

Would take time, but go into the modules and Explictly
make Objects like Recordsets, QueryDefs & Parameters
DAO objects....IE "DAO.Recordset".

It works for me.

HTH = Bob
 
Luis,

FYI, Microsoft dropped the "Q" designation from Knowledge Base articles
several years ago. The correct KB article number is 304318, not Q304318.

I suggest that you download the Access 2003 Conversion Toolkit:
http://www.microsoft.com/downloads/...76-5D89-450A-B977-980A9841111E&displaylang=en

Note: Try http://tinyurl.com/5qhae if the above link is broken due to
wrapping.

You may very well find that although you have hundreds of databases created
in Access 97 or earlier, many of them are not in active use. Concentrate on
converting the databases that have been used in the last few months.

Bob gave you some good advice to explicitely declare objects. Here is an
article that provides more background on this issue:

ADO and DAO Library References in Access Databases
http://www.access.qbuilt.com/html/ado_and_dao.html

Generally, conversions go fairly smooth, but you can run into snags. For
example, one can use =Null in JET 3.5x, but this will not work in JET 4.0
databases. It is always best to ensure that any VBA code compiles without
errors prior to attempting to convert the database. Fix any compile errors
first, before converting.

Your statement "....nothing is happening I can't even get into running it!"
does not give us any useful information to help you. We cannot see what is or
is not happening on your end. How familiar are you with VBA code, and running
functions from the Immediate window? Do you know how to place a break point
in the code? If so, what happens as you single step through the code, using
the F8 key?


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Luis,

I tried out the function in KB 304318, without using the alternative Shell
function method, and it worked okay for me. However, I made some alterations
to the code that I think are improvements. It's not perfect code, but I think
it is better than the original.

I recommend batch processing approx. 50 files at a time. This way, you
should not tie up your PC for too long of a time, and you should not exceed
the limit of the Immediate window. After finishing each batch of files, clear
the text printed to the Immediate window, move the source and converted files
to different folders, and add a new batch of source files to the source
folder.


Tom Wickerath
Microsoft Access MVP

'***********Begin Code*********************

' How to programmatically convert multiple Access databases (modified)
' http://support.microsoft.com/?id=304318
'
' Example usage:
' call ConvertDb("C:\DatabasesToConvert", "C:\ConvertedDatabases")

' Note: Did not test function with databases that include either a DB
password
' or User level security

Option Compare Database
Option Explicit

Sub ConvertDb(oldDbPath As String, newDbPath As String)
On Error GoTo ProcError

Dim strDBName As String
Dim strOLDDB As String
Dim strNewDb As String
Dim i As Long
Dim j As Long

If Len(Dir(newDbPath & "\*.MDB")) > 0 Then
'Delete any existing .mdb files in target directory
Kill newDbPath & "\*.MDB"
End If

strDBName = Dir(oldDbPath & "\*.MDB") ' Retrieve the first MDB file entry.

'Loop though the files in the folder to find MDB files.

Do While strDBName <> ""
' Ignore the current folder and the encompassing folder.
'If strDBName <> "." And strDBName <> ".." Then

If Right(strDBName, 3) = "mdb" Then
strOLDDB = oldDbPath & "\" & strDBName
strNewDb = newDbPath & "\" & strDBName

Application.ConvertAccessProject _
SourceFilename:=strOLDDB, _
DestinationFilename:=strNewDb, _
DestinationFileFormat:=acFileFormatAccess2000
i = i + 1
Debug.Print "Finished conversion of " & strDBName
End If
'End If
NextDB:
DoEvents
strDBName = Dir ' Get next MDB.
Loop

ExitProc:
MsgBox i & " databases were converted; " & _
j & " databases were compacted without converting."
Exit Sub
ProcError:
Select Case Err.Number
Case 2557 'File is already a JET 4 database
Debug.Print
Debug.Print "***" & strDBName & _
" was not converted because it is already a JET 4 database
***"
Application.CompactRepair strOLDDB, strNewDb
Debug.Print "Added a compacted copy of " & _
strDBName & " to " & newDbPath
j = j + 1
Debug.Print
Resume NextDB
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ConvertDb..."
End Select

Resume ExitProc
Resume
End Sub
 
First of all thanks to both of you for helping me with my problem. Regarding
my experience with VBA, well is basically none. I took VB.NET in college but
most of my work expirience is in Peoplesoft and Oracle, I just started
working here about a month ago. :)

So I would like to know what the Immediate window is and how to get into it?
Again thanks for your help and for the patience with me.

Luis Marrero
 
Ok guys I got the Inmediate window, but when I hit enter a compilation error
appears.
Compile error:
Expected variable or procedure, not project.
 
Hi Luis,

Just to be sure that we are talking the same Immediate window, you should be
able to open it by pressing the Control and G keys simultaneously (ie. Ctrl
G). You can also get to it by clicking on View > Immediate Window, when you
are in any code module.

Did you copy the code or retype it? If you retyped the code, you may have
made a typo. Click on Debug > Compile ProjectName (where ProjectName is the
name of your VBA project, likely the same name as your database). The code
should compile without any errors. If the menu option is shown as greyed out,
then your code is compiled.

When calling this function from the Immediate window, make sure to:
1.) use the Call keyword
2.) Include the name of the function, ConvertDb
3.) Include two valid paths, separated by a comma. Each path should be
enclosed in double quotes. These parameters should be enclosed in
parentheses, like this example:

call ConvertDb("C:\DatabasesToConvert", "C:\ConvertedDatabases")



Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
PS.
Make sure that the name of your VBA Project is *not* the same as the name of
any procedure (subroutine or function) in your database. I was just able to
reproduce the error that you reported, by changing the Project name to
ConvertDb. To change the Project name, click on Tools ProjectName
Properties..., where ProjectName is again a placeholder with the actual name
of the Project.

Also, make sure that the name of the module is not the same as the name of
any procedure in your database. A standard naming convention is to prefix the
name of all modules with lowercase "bas".


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Tom,

Yep DB name and module had the same name, that was the problem. You just
saved my life! :) Thanks for all the help. I'm sure you will see more of my
posts on the future.

Thanks,

Luis Marrero
 
Luis Marrero said:
Hello, I have looked up and down the forum but I can't find a thread that
helps me. My company have hundreds of DB created on Access 97, but guess
what they just desided to upgrade to 2k3. Ok, the easy way to go and do
it
is opening one by one all the databases but in reality that is not an
option
to me. So I was searching for tools to do this convertion, will doing my
search I found Q304318 in the microsoft support. It look very simple just
create a Module paste the code and run. I really try doing it the way its
explain there but i'm getting pretty frustrated with it and nothing is
happening I can't even get into running it!

I would like to ask for the help of all of you in this situation. Maybe
directing my with more detail instructions on how to do it or helping find
a
tool already created that will do the convertions. I'm trying to solve
this
problem as soon as I can so i have more time testing and correnting the
issues that are going to appear.

Thanks for any help any one can provide me.

Luis
Click to expand...
 
Luis Marrero said:
Hello, I have looked up and down the forum but I can't find a thread that
helps me. My company have hundreds of DB created on Access 97, but guess
what they just desided to upgrade to 2k3. Ok, the easy way to go and do
it
is opening one by one all the databases but in reality that is not an
option
to me. So I was searching for tools to do this convertion, will doing my
search I found Q304318 in the microsoft support. It look very simple just
create a Module paste the code and run. I really try doing it the way its
explain there but i'm getting pretty frustrated with it and nothing is
happening I can't even get into running it!

I would like to ask for the help of all of you in this situation. Maybe
directing my with more detail instructions on how to do it or helping find
a
tool already created that will do the convertions. I'm trying to solve
this
problem as soon as I can so i have more time testing and correnting the
issues that are going to appear.

Thanks for any help any one can provide me.

Luis
Click to expand...
 
Hey Tom,

Yes, I download it and ran it yesterday. All the warning it show were
because of security. Since I'm doing this on the test server i'm going to
remove the security of the databases and run the test again and lets see what
happens.

With the code you provided me a created a little applicaton so if someone
else here its going to help me with at least one of the server its easier for
them to do the convertion. Nothing fancy just a form with a button that says
convert, and a message that said where the databases should and where the
converted will be. Also says not to try and do more and 50 per run.

Thanks,

Luis
 
Ok, i presented today my application to the manager and he was loving it.
When I explained to him about why only doing 50 at a time yada yada... He
asked me if there was a way to modify the program so that it will go thru all
the directory including the subdirectories and converting the files. If no
that's ok but if you find a way that's really good, he said. So I been
toying with the code and here is how it stands right now. Its not finding
any .MDB here is the code:

Option Compare Database
Option Explicit

Sub ConvertDb(newDbPath As String)
On Error GoTo ProcError

Dim strDBName As String
Dim strOLDDB As String
Dim strNewDb As String
Dim vItem As Variant
Dim i As Long
Dim j As Long
Dim txtDbToConvert As String
Dim txtSearchPath As String

If Len(Dir(newDbPath & "\*.MDB")) > 0 Then
'Delete any existing .mdb files in target directory
Kill newDbPath & "\*.MDB"
End If

txtDbToConvert = "*.MDB"
txtSearchPath = [Forms]![Main]![txtSearchPath]

With Application.FileSearch
.FileName = txtDbToConvert
.LookIn = txtSearchPath
.SearchSubFolders = True
.Execute

For Each vItem In .FoundFiles
strNewDb = newDbPath & "\" & strDBName

Application.ConvertAccessProject _
SourceFilename:=vItem, _
DestinationFilename:=strNewDb, _
DestinationFileFormat:=acFileFormatAccess2002
i = i + 1
Debug.Print "Finished conversion of " & strDBName
strDBName = Dir
Next vItem
End With

ExitProc:
MsgBox i & " databases were converted; " & _
j & " databases were compacted without converting."
Exit Sub
ProcError:
Select Case Err.Number
Case 2557 'File is already a JET 4 database
Debug.Print
Debug.Print "***" & strDBName & _
" was not converted because it is already a JET 4 database "
Application.CompactRepair vItem, strNewDb
Debug.Print "Added a compacted copy of " & _
strDBName & " to " & newDbPath
j = j + 1
Debug.Print
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ConvertDb..."
End Select
Resume ExitProc
Resume
End Sub

THanks,

Luis
 
Hi Luis,

The reason your revised code does not work is that the variable strDBName is
an uninitialized zero length string:

strNewDb = newDbPath & "\" & strDBName

One modification that you could make would be the following:
strNewDb = newDbPath & Mid$(vItem, InStrRev(vItem, "\"))

This will work, as long as you do not have two databases with identical
names in different child folders, since your code is saving all converted
databases to the same target folder.

I think in this case, it would be better to concatenate something onto the
filename, such as the word "Converted", and store the converted database in
the same folder as the source database. Try the following modifications as a
starting point:

Sub ConvertDb() '<---Remove parameter for target path

'Comment the following code out (or delete it):
'If Len(Dir(newDbPath & "\*.MDB")) > 0 Then
' 'Delete any existing .mdb files in target directory
' Kill newDbPath & "\*.MDB"
'End If

For Each vItem In .FoundFiles
strNewDb = Mid$(vItem, 1, InStrRev(vItem, "\") - 1) & _
"\Converted" & Mid$(vItem, InStrRev(vItem, "\") + 1)


Debug.Print "Finished conversion of " & vItem
'Remove the following two lines of code
'Debug.Print "Finished conversion of " & strDBName
'strDBName = Dir

' Comment out the following lines in ProcError:, for Case 2557, since you
' cannot compact to the same folder:

' Application.CompactRepair vItem, strNewDb
' Debug.Print "Added a compacted copy of " & _
' strDBName & " to " & newDbPath
' j = j + 1




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Luis Marrero said:
Ok, i presented today my application to the manager and he was loving it.
When I explained to him about why only doing 50 at a time yada yada... He
asked me if there was a way to modify the program so that it will go thru all
the directory including the subdirectories and converting the files. If no
that's ok but if you find a way that's really good, he said. So I been
toying with the code and here is how it stands right now. Its not finding
any .MDB here is the code:

Option Compare Database
Option Explicit

Sub ConvertDb(newDbPath As String)
On Error GoTo ProcError

Dim strDBName As String
Dim strOLDDB As String
Dim strNewDb As String
Dim vItem As Variant
Dim i As Long
Dim j As Long
Dim txtDbToConvert As String
Dim txtSearchPath As String

If Len(Dir(newDbPath & "\*.MDB")) > 0 Then
'Delete any existing .mdb files in target directory
Kill newDbPath & "\*.MDB"
End If

txtDbToConvert = "*.MDB"
txtSearchPath = [Forms]![Main]![txtSearchPath]

With Application.FileSearch
.FileName = txtDbToConvert
.LookIn = txtSearchPath
.SearchSubFolders = True
.Execute

For Each vItem In .FoundFiles
strNewDb = newDbPath & "\" & strDBName

Application.ConvertAccessProject _
SourceFilename:=vItem, _
DestinationFilename:=strNewDb, _
DestinationFileFormat:=acFileFormatAccess2002
i = i + 1
Debug.Print "Finished conversion of " & strDBName
strDBName = Dir
Next vItem
End With

ExitProc:
MsgBox i & " databases were converted; " & _
j & " databases were compacted without converting."
Exit Sub
ProcError:
Select Case Err.Number
Case 2557 'File is already a JET 4 database
Debug.Print
Debug.Print "***" & strDBName & _
" was not converted because it is already a JET 4 database "
Application.CompactRepair vItem, strNewDb
Debug.Print "Added a compacted copy of " & _
strDBName & " to " & newDbPath
j = j + 1
Debug.Print
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ConvertDb..."
End Select
Resume ExitProc
Resume
End Sub

THanks,

Luis
Click to expand...
 
Tom,

Thanks for all your help. Everything is working perfectly!

Luis Marrero
Applications Developer
Certa Data corp.
www.certipay.com

Tom Wickerath said:
Hi Luis,

The reason your revised code does not work is that the variable strDBName is
an uninitialized zero length string:

strNewDb = newDbPath & "\" & strDBName

One modification that you could make would be the following:
strNewDb = newDbPath & Mid$(vItem, InStrRev(vItem, "\"))

This will work, as long as you do not have two databases with identical
names in different child folders, since your code is saving all converted
databases to the same target folder.

I think in this case, it would be better to concatenate something onto the
filename, such as the word "Converted", and store the converted database in
the same folder as the source database. Try the following modifications as a
starting point:

Sub ConvertDb() '<---Remove parameter for target path

'Comment the following code out (or delete it):
'If Len(Dir(newDbPath & "\*.MDB")) > 0 Then
' 'Delete any existing .mdb files in target directory
' Kill newDbPath & "\*.MDB"
'End If

For Each vItem In .FoundFiles
strNewDb = Mid$(vItem, 1, InStrRev(vItem, "\") - 1) & _
"\Converted" & Mid$(vItem, InStrRev(vItem, "\") + 1)


Debug.Print "Finished conversion of " & vItem
'Remove the following two lines of code
'Debug.Print "Finished conversion of " & strDBName
'strDBName = Dir

' Comment out the following lines in ProcError:, for Case 2557, since you
' cannot compact to the same folder:

' Application.CompactRepair vItem, strNewDb
' Debug.Print "Added a compacted copy of " & _
' strDBName & " to " & newDbPath
' j = j + 1




Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________


Luis Marrero said:
Ok, i presented today my application to the manager and he was loving it.
When I explained to him about why only doing 50 at a time yada yada... He
asked me if there was a way to modify the program so that it will go thru all
the directory including the subdirectories and converting the files. If no
that's ok but if you find a way that's really good, he said. So I been
toying with the code and here is how it stands right now. Its not finding
any .MDB here is the code:

Option Compare Database
Option Explicit

Sub ConvertDb(newDbPath As String)
On Error GoTo ProcError

Dim strDBName As String
Dim strOLDDB As String
Dim strNewDb As String
Dim vItem As Variant
Dim i As Long
Dim j As Long
Dim txtDbToConvert As String
Dim txtSearchPath As String

If Len(Dir(newDbPath & "\*.MDB")) > 0 Then
'Delete any existing .mdb files in target directory
Kill newDbPath & "\*.MDB"
End If

txtDbToConvert = "*.MDB"
txtSearchPath = [Forms]![Main]![txtSearchPath]

With Application.FileSearch
.FileName = txtDbToConvert
.LookIn = txtSearchPath
.SearchSubFolders = True
.Execute

For Each vItem In .FoundFiles
strNewDb = newDbPath & "\" & strDBName

Application.ConvertAccessProject _
SourceFilename:=vItem, _
DestinationFilename:=strNewDb, _
DestinationFileFormat:=acFileFormatAccess2002
i = i + 1
Debug.Print "Finished conversion of " & strDBName
strDBName = Dir
Next vItem
End With

ExitProc:
MsgBox i & " databases were converted; " & _
j & " databases were compacted without converting."
Exit Sub
ProcError:
Select Case Err.Number
Case 2557 'File is already a JET 4 database
Debug.Print
Debug.Print "***" & strDBName & _
" was not converted because it is already a JET 4 database "
Application.CompactRepair vItem, strNewDb
Debug.Print "Added a compacted copy of " & _
strDBName & " to " & newDbPath
j = j + 1
Debug.Print
Case Else
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ConvertDb..."
End Select
Resume ExitProc
Resume
End Sub

THanks,

Luis
Click to expand...
Click to expand...
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Converting Database from 97 to 2007 1
How to Convert EML to PST in 2025? 2
Convert Access database to 97 1
Converting Access 97 to Acees 2003 & get conversion Errors 1
MDE issues after upgrade from 2K to 2K3 10
Making a switchboard in an Access 2k3 Project 3
Converting An Access 2007 database to Access 97 1
How to Convert EML to PST? 2

Back
Top