Solved

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

Posted on 2004-08-30
11
495 Views
Last Modified: 2013-12-05
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?  
0
Comment
Question by:Masta120
  • 5
  • 4
  • 2
11 Comments
 
LVL 7

Expert Comment

by:jacobhoover
ID: 11936053
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.
0
 
LVL 1

Author Comment

by:Masta120
ID: 11936167
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.
0
 
LVL 7

Expert Comment

by:jacobhoover
ID: 11936460
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.
0
 
LVL 1

Author Comment

by:Masta120
ID: 11936666
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?
0
 
LVL 5

Expert Comment

by:WesleySaysHi
ID: 11936845
"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
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 5

Expert Comment

by:WesleySaysHi
ID: 11936895
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
0
 
LVL 7

Accepted Solution

by:
jacobhoover earned 350 total points
ID: 11937455
Hmm, I have found:
http://www.experts-exchange.com/Databases/MS_Access/Q_21027623.html

Which links to:
http://support.microsoft.com/default.aspx?scid=http://support.microsoft.com:80/support/kb/articles/q202/0/39.asp&NoWebContent=1

But it all seems like a bunch of crapola to me.  It appears as if MS didn't want you to be able to automate this process.  MS's solution is weak if not totally buggy.  The only thing I could think of would be with:
app.DoCmd.RunCommand acCmdMakeMDEFile
However I would store the app.hWndAccessApp and use it to enum it's child windows and look for the dialog window which is requesting the user select the file.  Then manually set it's value and then send the enter key....  But that is ugly..  The other option would be to go through all the options for the SysCmd and see if you get lucky.. But it's like russian roulett.
0
 
LVL 7

Expert Comment

by:jacobhoover
ID: 11937849
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
0
 
LVL 7

Expert Comment

by:jacobhoover
ID: 11937874
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... :/
0
 
LVL 1

Author Comment

by:Masta120
ID: 11940931
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.
0
 
LVL 1

Author Comment

by:Masta120
ID: 11941613
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.
0

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
In this fifth video of the Xpdf series, we discuss and demonstrate the PDFdetach utility, which is able to list and, more importantly, extract attachments that are embedded in PDF files. It does this via a command line interface, making it suitable …

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now