?
Solved

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

Posted on 2004-08-30
11
Medium Priority
?
507 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
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
 
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 1050 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
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 …
Simple Linear Regression
Suggested Courses

764 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