asked on

Help with converting an Access 2002 MDB to an MDE using VB6

I am having a problem converting MS Access *.mdb files into *.mde files using Visual Basic 6. I am still fairly new to VB6 and I found some code on the web after a LOT of searching, so please bear with me....

I created a console VB application that takes an input *.mdb file and generates the *.mde output file. The main lines of code that do the conversion are as follows:

Dim x As Object
Dim strFileIn As String
Dim strFileOut As String

' I gave the strings some absolute file paths

Set x = CreateObject("Access.Application")
x.SysCmd 603, strFileIn, strFileOut

Now, this works perfectly for *.mdb files when I have MS Access 97 installed on the machine. I tried to run this on a machine that has MS Access 2002 installed, and this didn't work. It didn't give me an error, it just did not do anything.

Now, I need this to be a console based application (does NOT have to be VB6). I am going to write a batch file that will call the executable for my application because I need to do this often for many files.

I tried doing the same thing in a VBScript file (*.vbs), and calling "cscript.exe" to do the conversion but again, I had the same problem. The script worked perfectly on my Access 97 machine, but did not do anything on my Access 2002 machine.

I also made sure that my input mdb file was created with MS Access 2002 on my Access 2002 machine.

Does anyone know what I am doing wrong or what I can do to fix this problem?

jacobhoover

I can pretty much assure you the problem is with the line:
x.SysCmd 603, strFileIn, strFileOut

Odds are MS changed the literal value for the command, i.e. 603...

I will take a look and see if I can get you more info on the new constant.

Masta120

ASKER

Thanks a lot.....

I have done all sorts of web searching and I could not find it.....

I did read somewhere that "603" is an undocumented feature of the SysCmd function.

jacobhoover

Another option, are you sure the databases are in the proper format?

I.E. Access 2002 can open a Access 2k database without conversion but you can't convert them to MDE's without saving them as the 2002 format first.

P.S. Any undocumented feature is sure to break as that is why they are not documented... BUT there has to be a way of determining the new calling conventions.

Masta120

ASKER

Yeah, I am sure my db's are Access 2002 format.

Do you know of the new calling conventions? How/where could I look it up?

WesleySaysHi

"Now, this works perfectly for *.mdb files when I have MS Access 97 installed on the machine"

I understand from this that you made the MDB file on your computer that has Access 97 installed.

"I tried to run this on a machine that has MS Access 2002 installed, and this didn't work"

This means that you are trying to convert your Access 97 MDB file to an Access 2002 MDE file. It will never work!

This is what you have to do:

1. Take the original Access 97 MDB file and convert it to an Access 2002 MDB file;
2. Do a debug>compile to your MDB and fix all errors;
3. Convert MDB to MDE, it should work now.

Don't forget to save a copy of your original MDB file.

Regards,
Wesley

WesleySaysHi

One more thing:

Try to install MDAC if MDB works well and no conversion. See also if 'Make MDE' is grayed out when using Access 2002.

Good luck,
Wesley

ASKER CERTIFIED SOLUTION

jacobhoover

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jacobhoover

Hmmm, can you use .Net?

If so this works with access 2k.

Imports System

Module ModGlobal
      Public t As Threading.Thread
      Public App As Access.Application
      Public hwnd As IntPtr
      Public szFile As String

      Public Const GW_CHILD As Long = 5
      Public Const GW_HWNDNEXT As Long = 2
      Public Const WM_COMMAND As Long = &H111
      Public Const WM_SETTEXT As Long = &HC
      Public Const WM_KEYDOWN As Long = &H100
      Public Const WM_KEYUP As Long = &H101
      Public Const VK_RETURN As Long = &HD

      Public Declare Auto Function SendMessage Lib "user32.dll" (ByVal hWnd As IntPtr, _
      ByVal wMsg As Int32, _
      ByVal wParam As Int32, _
      ByVal s As String _
      ) As Int32

      Public Declare Auto Function GetWindow Lib "user32" _
      (ByVal hwnd As IntPtr, _
       ByVal wCmd As Long) As IntPtr
      Public Declare Auto Function FindWindow Lib "user32.dll" (ByVal lpClassName As String, _
       ByVal lpWindowName As String) As IntPtr
      Public Declare Auto Sub Sleep Lib "kernel32" Alias "Sleep" (ByVal dwMilliseconds As Long)
      Sub Main()
            MakeMDE("C:\TEMP\pd\db1.mdb")
      End Sub
      Public Sub Prepare()
            '// ASSUMING YOU ARE USING EARLY BINDING
            App = New Access.Application()
            App.Visible = True
      End Sub
      Public Sub Convert()
            'hwnd = App.Application.hWndAccessApp
            App.DoCmd.RunCommand(Access.AcCommand.acCmdMakeMDEFile)
      End Sub
      Public Sub MakeMDE(ByVal mdb As String)
            '// SANITY CHECKS
            If Dir(mdb) = "" Then Exit Sub
            Prepare()
            t = New Threading.Thread(AddressOf FNAccessComm)
            szFile = mdb
            t.Start()
            Convert()
      End Sub
      Sub FNAccessComm()
            Sleep(1000)
            'Main Convert Win Class: bosa_sdm_Mso96
            ' Secondary window is:RichEdit20W
            hwnd = FindWindow("bosa_sdm_Mso96", vbNullString)
            Dim hWndChild As IntPtr = GetWindow(hwnd, GW_CHILD)
            hWndChild = GetWindow(hWndChild, GW_HWNDNEXT)
            hWndChild = GetWindow(hWndChild, GW_HWNDNEXT)
            hWndChild = GetWindow(hWndChild, GW_HWNDNEXT)
            hWndChild = GetWindow(hWndChild, GW_HWNDNEXT)
            '            SetWindowText(hWndChild.ToInt32, szFile) WON'T WORK ACROSS PROCESS BOUNDARIES
            SendMessage(hWndChild, WM_SETTEXT, IntPtr.Zero.ToInt32, szFile)
            '// ACCEPT FILE
            SendMessage(hWndChild, WM_KEYDOWN, VK_RETURN, 0)
            SendMessage(hWndChild, WM_KEYUP, VK_RETURN, 0)
            '// WAIT FOR SAVE AS DIALOG
            Sleep(1000)
            '// LOOK FOR IT ALL OVER AGAIN
            hwnd = FindWindow("bosa_sdm_Mso96", vbNullString)
            hWndChild = GetWindow(hwnd, GW_CHILD)
            hWndChild = GetWindow(hWndChild, GW_HWNDNEXT)
            hWndChild = GetWindow(hWndChild, GW_HWNDNEXT)
            hWndChild = GetWindow(hWndChild, GW_HWNDNEXT)
            hWndChild = GetWindow(hWndChild, GW_HWNDNEXT)
            '// ACCEPT DEFAULT MDB
            SendMessage(hWndChild, WM_KEYDOWN, VK_RETURN, 0)
            SendMessage(hWndChild, WM_KEYUP, VK_RETURN, 0)
      End Sub
End Module

jacobhoover

The only reason I ask about the .Net ability is because the COM call to App.DoCmd.RunCommand(Access.AcCommand.acCmdMakeMDEFile) is blocking which means your thread is locked till it get's back from the conversion.

If it dosen't work, it's probably because MS changed the class name between Access 2k and 2002, which I don't have access to at the moment.

Time for SLEEP... :/

Masta120

ASKER

Hmmm.....I don't have access to VB.NET just yet.

And, I need it to be Access 2002.

I don't think it should be a big deal for me if the thread is blocked. With Access 97, the conversion for each db takes a second or two at most. I am hoping it is as quick with 2002 (if we can ever get it working) :).

And ideally, I am going to run this app from a batch file. I plan to pass the input db file name to the app as a command line argument, and build this batch file to do this many times for many different files.

Masta120

ASKER

Thanks a lot guys. I figured it out. I went to the links you posted jacobhoover and found what my mistake was. It was actually a one line fix.

The x.SysCmd 603 worked, the only thing I had done incorrectly was the way I declared x. Originally I declared it of type "Object", then set it to a "New Access.Application". I needed to declare it of type "Access.Application", and then set it to a "New Access.Application".

Thanks again guys for your help.