Help with VB.NET and MS Access 2002

[Masta120]

I needed to be able to convert MS Access 2002 *.mdb files into *.mde (self executing) files from a console based app. I needed this to be a console application, basically a separate executable, so I cannot use VBA. I know the code to do this in VB6, but do not know how to in VB.NET.

Problem is that VB6 will only generate Access 97 files, not 2002. The code I used under VB6 is:

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

I tried this code under VB.NET but I could not get it to work correctly. Does anyone know what I am doing wrong? Should the code be the same for VB.NET? Is there another (or better) way to convert these mdb files to mde files using .NET (does not have to be VB)? I am going to need to do this often, for a lot of files.

[MKoslof]

Hmmm...not sure how you would do this in .Net. You can still instaniate COM objects via CreateObject, though it is not the preferred method in the .Net namespace.

What is the error you receive? Let me try to do some testing on my end.

[Masta120]

Well, in VB.NET, I was having a lot of problems. I couldn't get even simple stuff to work (yeah, I am a newbie to .NET).

Even still, is .NET the best way to go? Is it possible to use a VB script? I tried my code in a VB script and it still didn't work on the machine with Access 2002 installed, but worked perfectly on my Access 97 machine. How about C++? I tried to implement automation in VC++ (version 6, not .NET) but was having problems with that also.

Well, any help that can be provided is MUCH appreciated....

[MKoslof]

Let me see what I can come up with For .Net. I don't think the version of Access has anything to do with whether or not it is physically possible. Access 97 uses the old Jet 3.51 provider, while Access 2000 and higher uses the Jet 4.0 Provider so the engine itself changed. Meaning, I am confident you can convert an .mdb file to a .mde file if using VB (*at least I think you can) if you are working with a newer version of Access.

The same applies with VB Script, you would be able to envoke the same methods and COM objects. .Net will also allow you to reference COM objects (it is in the Com object references tab) or use late binding via CreateObject(). Now, I don't think there will be any native .Net method that will do this kind of conversion. So, the API or COM object route *might* be your best alternative in the .Net namespace. I have not tried anything like this, so give me some time to see what I can come up with. I have Access XP to test with and 2003.

[Masta120]

Well, like I said, I used that code sample that I posted and built a console based executable with VB6. It worked perfectly on my machine with Access 97.

I then brought over the same executable (code sample I posted) to my machine with Access 2002 (the same machine also still has Access 97 installed), along with my mdb file that was created in Access 97. The executable did not do anything on the Access 2002 pc. I then tried to convert the mdb into a 2000 mdb and a 2002 mdb. Still, the executable did not do anything. It ran, with no errors, no output messages, just nothing happened.

I tried the same with the VB script I used on my Access 97 machine. I tried using it on my Access 2002 machine (with the "cscript" command), with each mdb version (97, 2000, and 2002). Again, the script ran, with no errors, and just did not do anything.

Thats why I thought VB.NET would be my solution. I would prefer to keep this in VB6 if I could, but I would use .NET if I had to.

[MKoslof]

So this is your current code that works with only Access 97:



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



Let me see what I can come up with..again, COM Interop is not exactly the best method within .Net. But at the same time, I don't think you are going to find any .Net native methods to do this either. All replication and conversion classes are not tailored to Access...MDB to MDE conversion is not something .Net will understand automatically.

[MKoslof]

OK, there has been some confusion as to where to put this thread. Masta can you clarify if you want to use VB or .Net for this? From your last comments it appears you want to use VB as your top choice..is that correct? If so, this post would be best suited for the Legacy Database forum. If you want to use .Net this is best suited for one of the .Net forums. Until we get clarification I will leave it in this forum (VBA Integration) so some of the other VBA/Access guys can take a look. Please let us know, best case scenerio what language or platform you want to use for this. Thanks

[MKoslof]

Grrr...I see your frusteration, either using the SysCmd or the DoCmd.RunCommand acCmdMakeMDEFile method will WORK for Access 97 and 2000.

However Access XP does not support either method. You might need a service pack upgrade..not sure. It appears XP has changed its security structure and this is not documented anywhere that I can find. You might be able to get really complicated and work around this..I haven't had success yet with Access 2002. That is the root of your problem..it has nothing to do with VB, .Net or VBA..it is something within XP. And this is quite strange, considering 2000 and XP basically have the same Jet Provider functionality...it is must be something within the internal security settings not exposed....

[MKoslof]

OK, you gotta love Access.. :) this will work.

First, make sure you have done this:

http://support.microsoft.com/default.aspx?scid=kb;en-us;278376

Note, I had a NEW database that I created in XP...but I STILL had to convert this to 2002 format...not sure why, but what can do is automate the whole procedure, do a DoCmd.RunCommand method in order to convert the Database...or do it by hand if you can afford to.

Then, you MUST make sure all code in modules is fully qualified, meaning you have explicit calls to ADODB.connection, DAO.Database, etc. Also, make sure you have Option Explicit in ALL modules..if not, the conversion will fail as well.

Then, you can use this from VB to automate the MDE conversion (.NEt as well via COM Interop)



Function GenerateMDEFile(MyPath As String)

On Error Resume Next

Dim Acc As Access.Application

Set Acc = CreateObject("Access.Application")

SendKeys MyPath & "{Enter}{Enter}"
SendKeys "{Enter}"

Acc.DoCmd.RunCommand acCmdMakeMDEFile

Set Acc = Nothing

MsgBox "done"

End Function

[Wamphyri]

Just to add. I don't have any success with access 2000 when using the CreateObject method. However explicitly adding a reference to access 2000 and using
The Syscmd 603 doesn't work when using

Dim x As Object
Set x = CreateObject("Access.Application")

It works using (perhaps you could try that on 2002)

Dim x as Access.Application
set x = New Access.Application

[MKoslof]

I am using 2002 and that worked for me fine...I have a reference to the Access Object library in my project file...it actually worked using late binding and early binding. For CreateObject to work, you might need to add the qualified version.



accApp = CreateObject("Access.Application.9")

[Masta120]

Hey guys,

Thanks again for your prompt responses. I really appreciate it.

But, I am still having problems. What I was trying to do was write a console app, that I can run and pass a file name to. Then this console executable will then create this mde file. I was going to set up a batch file, that would call this executable, since I need to do this often and for a lot of files.

MKoslof, I tried your code, and I think I am still doing something wrong. But, the function call to "DoCmd.RunCommand acCmdMakeMDEFile", I thought this was mainly for VBA, running VB inside of an Access database. I did try this in my VB6 console app, and there were problems.

One of the problems was VB6 didn't like "Dim Acc As Access.Application". It could not recognize the type for Acc.

You had said you got this working? From a standalone VB app? Was it with VB.NET? I don't have VB.NET here so I will try it when I get home today.

[MKoslof]

I ran this from Legacy VB and .NEt.

In your VB project, go to references...select the Microsoft Access Object Library v10.0 (if Access XP) now you should be able to qualify the Access.Application object.

If you want to have a batch file call the VB executable I don't see anything wrong with that..all object references would be fully compiled within the exe.

[Masta120]

I tried this in VB6, and selected MS Access Object Library. I copied the code exactly as you posted it, called "GenerateMDEFile" in my sub main, and it compiled fine. But when I ran it, it launched a file dialog window prompting me for the file name to save my MDE to. I tried using sendkeys to send another {ENTER} but it didn't have any effect.

Is there a way to get around that last window through code? I didn't want to have any manual intervention when running the executable...

[MKoslof]

Yes, I noticed that too. This ONLY occurs when the mde file already exists..so it is prompting you to rename it (it appears it will not overwrite). Basically this code names the mdb in the same directory..so if you pass C:\db1.mdb as the database it creates a new file called C:\db1.mde. So you probably already have this mde file on your CPU..that is why it is prompting you.

To get around this, you can use this function and check if the mde file already exists, if so, back out and tell the user to try again with a different name. See below....



Private Sub Command2_Click()
'call the procedure passing in the database name
GenerateMDEFile ("C:\db2.mdb")
End Sub

Public Function FileExists(ByVal FileSpec As String) As Boolean
On Error Resume Next
FileExists = (GetAttr(FileSpec) And vbDirectory) = vbNormal
End Function

Function GenerateMDEFile(MyPath As String)

On Error Resume Next

Dim NAcc As Access.Application
Dim MDEName As String

Set NAcc = New Access.Application

'here switch the mdb extension with .mde
'test if it has already been created

MDEName = Left$(MyPath, Len(MyPath) - 4) & ".mde"

If Not FileExists(MDEName) Then
SendKeys MyPath & "{Enter}{Enter}"
SendKeys "{Enter}"
NAcc.DoCmd.RunCommand acCmdMakeMDEFile
Else
MsgBox "this mde file already exists, specify a different name."
Exit Function
End If

Set NAcc = Nothing

End Function

[Masta120]

Ok MKoslof, I FINALLY got it to work :) ! Well not completely. I got your code to work. Mine is still not perfect. Like I said before, I am trying to do all of this from a console app. When I run the app from the command line (I open up a command window, change directories to my VB workspace, and run my executable), it goes to the next line, and then it echos the input path I specified at the command line as a parameter. Then it just gets hung up there and freezes. I have to kill 2 processes in the task manager, "msaccess.exe", and "mde.exe" (my executable name). Furthermore, before killing "msaccess.exe" while it was frozen, I tried to double click on the database I was using as an input to open it. When I did, it prompted me with the file dialog window, asking me what name do I want to save my MDE as. After killing the 2 processes, I get the "Done" msgbox (you'll see in my code below) and the command prompt comes back to life.

I am assuming something is not right with the "SendKeys" in my console app. Seeing the echo on the next line makes me think the "SendKeys" is going to the DOS window instead of the Access window.

Here is the code I am using. Any ideas?



Option Explicit

Private Declare Function GetStdOutHandle Lib "kernel32" Alias "GetStdHandle" _
(Optional ByVal HandleType As Long = -11) As Long

Private Declare Function WriteFile Lib "kernel32" (ByVal hFile As Long, _
ByVal lpBuffer As String, ByVal cToWrite As Long, ByRef cWritten As Long, _
Optional ByVal lpOverlapped As Long) As Long
Private Function ConsoleWrite(sText As String) As Long
WriteFile GetStdOutHandle, ByVal sText, Len(sText), ConsoleWrite
End Function

Public Sub Main()

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

On Error Resume Next
arg = Command()

strFileIn = arg
'strFileIn = "d:\temp\db1_02.mdb"

' get the output file to kill if it exists
strFileOut = Left(strFileIn, Len(strFileIn) - 1)
strFileOut = strFileOut + "e"

Kill strFileOut

GenerateMDEFile (strFileIn)

MsgBox "Done"

End Sub

Public Function FileExists(ByVal FileSpec As String) As Boolean
On Error Resume Next
FileExists = (GetAttr(FileSpec) And vbDirectory) = vbNormal
End Function

Function GenerateMDEFile(MyPath As String)
On Error Resume Next

Dim NAcc As Access.Application
Dim MDEName As String

Set NAcc = New Access.Application

MDEName = Left$(MyPath, Len(MyPath) - 4) & ".mde"

If Not FileExists(MDEName) Then
SendKeys MyPath & "{Enter}{Enter}"
SendKeys "{Enter}"
NAcc.DoCmd.RunCommand acCmdMakeMDEFile
Else
Exit Function
End If

Set NAcc = Nothing

End Function

[MKoslof]

UG..well instead of using SendKeys you can try using API calls such as SendMessage to simulate key presses. From your console app you might get better results that way.

Basically use FindWindow() to actually track down the Access Window. Then once you have the required window, you can use sendmessage to send key strokes to the activated applicaition.