zachar
asked on
MDB to MDE by VB code.
How can i convert Access MDB file into MDE file by VB code? (except SendKeys)
ASKER
I tried "DoCmd.RunCommand acCmdMakeMDEFile" from within DB - i got "Run time Error 7807 - can not make MDE while running macro or VB code..."
My friend, this is you only option in VB6:
Sub MakeMDEFile
Dim AccApp as New Access.Application
AccApp.RunCommand acCmdMakeMDEFile
End Sub
The above will open 2 dialog boxes:
1. Choose the source DB
2. Choose the Destinations DB
There is no other way beside API to do this more automated.
Sub MakeMDEFile
Dim AccApp as New Access.Application
AccApp.RunCommand acCmdMakeMDEFile
End Sub
The above will open 2 dialog boxes:
1. Choose the source DB
2. Choose the Destinations DB
There is no other way beside API to do this more automated.
ASKER
I have do to it 100% programmatically - no dialog boxes during the execution of the code.
You can use Microsoft Visual Basic for Applications to create an MDE file from your existing Microsoft Access database. You can run the following sample function from any database other than the database that you want to convert to an MDE file. This function has one argument, MyPath, the full path to the database from which to create the MDE file.
NOTE: You can use the SendKeys method to bypass the dialog box. Although this method allows you to programmatically save a database as an MDE file, it may cause an errant "ENTER" to be processed somewhere in one of your programs or open files, so use this method with caution.
Create a new module, and then type the following function:
Function GenerateMDEFile(MyPath As String)
Dim NAcc As Access.Application
Set NAcc = CreateObject("Access.Appli
'The following lines simulate accepting the default
'name, clicking Make MDE, and clicking Save
SendKeys MyPath & "{Enter}{Enter}"
SendKeys "{Enter}"
NAcc.DoCmd.RunCommand acCmdMakeMDEFile
Set NAcc = Nothing
End Function
To run this function, do the following:
Click Immediate Window on the View menu to open the Immediate window.
Type ?GenerateMDEFile(<Path&DBN
NOTE: The resulting MDE file will have the same name as the original database.
NOTE: You can use the SendKeys method to bypass the dialog box. Although this method allows you to programmatically save a database as an MDE file, it may cause an errant "ENTER" to be processed somewhere in one of your programs or open files, so use this method with caution.
Create a new module, and then type the following function:
Function GenerateMDEFile(MyPath As String)
Dim NAcc As Access.Application
Set NAcc = CreateObject("Access.Appli
'The following lines simulate accepting the default
'name, clicking Make MDE, and clicking Save
SendKeys MyPath & "{Enter}{Enter}"
SendKeys "{Enter}"
NAcc.DoCmd.RunCommand acCmdMakeMDEFile
Set NAcc = Nothing
End Function
To run this function, do the following:
Click Immediate Window on the View menu to open the Immediate window.
Type ?GenerateMDEFile(<Path&DBN
NOTE: The resulting MDE file will have the same name as the original database.
This is for access 97, the above is Access 2000.
You can use Microsoft Visual Basic for Applications to create an MDE file from an existing Microsoft Access database. You can run the following sample function from any database other than the database that you want to convert to an MDE file. This function has one argument, MyPath, the full path to the database from which the MDE file will be created.
Create a new module, and then type the following function:
Function GenerateMDEFile(MyPath As String)
Dim NAcc As Access.Application
Set NAcc = CreateObject("Access.Appli
' The following lines simulate accepting the default
' name, clicking Make MDE, and clicking Save.
SendKeys MyPath & "{Enter}{Enter}"
SendKeys "{Enter}"
NAcc.DoCmd.RunCommand acCmdMakeMDEFile
Set NAcc = Nothing
End Function
To run this function, do the following:
On the View menu, click Debug Window, and then type the following line in the Debug window
?GenerateMDEFile(<Path&DBN
where <Path&DBName> is the full path and database name of the database from which you want to create an MDE file. Press ENTER.
NOTE: The resulting MDE file will have the same name as the original database.
WARNING: This example uses the SendKeys statement to bypass the dialog box. It is possible that this example will not work on your system because SendKeys simply sends the keystrokes as soon as the statement is processed and does not wait until the "right" moment to do so. Therefore, it's behavior is somewhat unpredictable and will vary from system to system. It may also cause an errant "ENTER" to be processed somewhere in one of your programs or open files, so use this method with caution.
Some experimentation may allow you to determine the right combination of keystrokes and events to open and close the dialog box without user intervention. This may require a different code segment than the one presented here. Remember, whatever solution you develop may work only on your machine, and may behave differently on another system.
You can use Microsoft Visual Basic for Applications to create an MDE file from an existing Microsoft Access database. You can run the following sample function from any database other than the database that you want to convert to an MDE file. This function has one argument, MyPath, the full path to the database from which the MDE file will be created.
Create a new module, and then type the following function:
Function GenerateMDEFile(MyPath As String)
Dim NAcc As Access.Application
Set NAcc = CreateObject("Access.Appli
' The following lines simulate accepting the default
' name, clicking Make MDE, and clicking Save.
SendKeys MyPath & "{Enter}{Enter}"
SendKeys "{Enter}"
NAcc.DoCmd.RunCommand acCmdMakeMDEFile
Set NAcc = Nothing
End Function
To run this function, do the following:
On the View menu, click Debug Window, and then type the following line in the Debug window
?GenerateMDEFile(<Path&DBN
where <Path&DBName> is the full path and database name of the database from which you want to create an MDE file. Press ENTER.
NOTE: The resulting MDE file will have the same name as the original database.
WARNING: This example uses the SendKeys statement to bypass the dialog box. It is possible that this example will not work on your system because SendKeys simply sends the keystrokes as soon as the statement is processed and does not wait until the "right" moment to do so. Therefore, it's behavior is somewhat unpredictable and will vary from system to system. It may also cause an errant "ENTER" to be processed somewhere in one of your programs or open files, so use this method with caution.
Some experimentation may allow you to determine the right combination of keystrokes and events to open and close the dialog box without user intervention. This may require a different code segment than the one presented here. Remember, whatever solution you develop may work only on your machine, and may behave differently on another system.
ASKER
Dear CareyMBilyeu! I wrote in my question - except SendKeys. (Please read carefully example you have copied from MSDN) Thank you anyway.
zachar,
I think CareyMBilyeu is right. and other choice is to find a utility for the "black work".
Ofer
I think CareyMBilyeu is right. and other choice is to find a utility for the "black work".
Ofer
ASKER
oavidov, CareyMBilyeu is not right because i have defined in the question that I DO NOT WANT TO USE "SendKeys" statement (please read the whole question, not only title). The 'black work' utility is exactly what i need to WRITE, not to FIND.
thank you.
thank you.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Cyber Beber, can you post a reference to the SysCmd "603". I have looked everywhere and can not find any reference to "603" creating a ".mde file as zachar requested. I would like to find the reference for future use. Here is all that I found on SysCmd:
SysCmd Method
You can use the SysCmd method to perform one of the following functions:
Display a progress meter or optional specified text in the status bar.
Return information about Microsoft Access and its associated files.
Return the state of a specified database object to indicate whether the object is open, is a new object, or has been changed but not saved.
For example, if you are building a custom wizard that creates a new form, you can use the SysCmd method to display a progress meter indicating the progress of your wizard as it constructs the form.
Syntax
ReturnValue = SysCmd(action[, text][, value])
ObjectState = SysCmd(action[, objecttype][, objectname])
The SysCmd method has the following arguments.
Argument Description
action One of the following intrinsic constants identifying the type of action to take.
The following set of constants applies to a progress meter. The SysCmd method returns a Null if these actions are successful. Otherwise, Microsoft Access generates a run-time error.
acSysCmdInitMeter Initializes the progress meter. You must specify the text and value arguments when you use this action.
acSysCmdUpdateMeter Updates the progress meter with the specified value. You must specify the text argument when you use this action.
acSysCmdRemoveMeter Removes the progress meter.
acSysCmdSetStatus Sets the status bar text to the text argument.
acSysCmdClearStatus Resets the status bar text.
The following set of constants provides information about Microsoft Access.
acSysCmdRuntime Returns True (–1) if a run-time version of Microsoft Access is running.
acSysCmdAccessVer Returns the version number of Microsoft Access.
acSysCmdIniFile Returns the name of the .ini file associated with Microsoft Access.
acSysCmdAccessDir Returns the name of the directory where Msaccess.exe is located.
acSysCmdProfile Returns the /profile setting specified by the user when starting Microsoft Access from the command line.
acSysCmdGetWorkgroupFile Returns the path to the workgroup file (System.mdw).
acSysCmdClearHelpTopic
The following constant provides information on the state of a database object.
acSysCmdGetObjectState Returns the state of the specified database object. You must specify the objecttype and objectname parts when you use this action value.
text A string expression identifying the text to be displayed left-aligned in the status bar. This argument is required when the action argument is acSysCmdInitMeter, acSysCmdUpdateMeter, or acSysCmdSetStatus; this argument isn't valid for other action argument values.
value A numeric expression that controls the display of the progress meter. This argument is required when the action argument is acSysCmdInitMeter; this argument isn't valid for other action argument values.
objecttype One of the following intrinsic constants:
acTable
acQuery
acForm
acReport
acMacro
acModule
acDataAccessPage
acDefault
acDiagram
acServerView
acStoreProcedure
This argument is required when the action argument is acSysCmdGetObjectState; this argument isn't valid for other action argument values.
objectname A string expression that is the valid name of a database object of the type specified by the objecttype argument. This argument is required when the action argument is acSysCmdGetObjectState; this argument isn't valid for other action argument values.
Remarks
By calling the SysCmd method with the various progress meter actions, you can display a progress meter in the status bar for an operation that has a known duration or number of steps, and update it to indicate the progress of the operation.
To display a progress meter in the status bar, you must first call the SysCmd method with the acSysCmdInitMeter action argument, and the text and value arguments. When the action argument is acSysCmdInitMeter, the value argument is the maximum value of the meter, or 100 percent.
To update the meter to show the progress of the operation, call the SysCmd method with the acSysCmdUpdateMeter action argument and the value argument. When the action argument is acSysCmdUpdateMeter, the SysCmd method uses the value argument to calculate the percentage displayed by the meter. For example, if you set the maximum value to 200 and then update the meter with a value of 100, the progress meter will be half-filled.
You can also change the text that's displayed in the status bar by calling the SysCmd method with the acSysCmdSetStatus action argument and the text argument. For example, during a sort you might change the text to "Sorting...". When the sort is complete, you would reset the status bar by removing the text. The text argument can contain approximately 80 characters. Because the status bar text is displayed by using a proportional font, the actual number of characters you can display is determined by the total width of all the characters specified by the text argument.
As you increase the width of the status bar text, you decrease the length of the meter. If the text is longer than the status bar and the action argument is acSysCmdInitMeter, the SysCmd method ignores the text and doesn't display anything in the status bar. If the text is longer than the status bar and the action argument is acSysCmdSetStatus, the SysCmd method truncates the text to fit the status bar.
You can't set the status bar text to a zero-length string (" "). If you want to remove the existing text from the status bar, set the text argument to a single space. The following examples illustrate ways to remove the text from the status bar:
varReturn = SysCmd(acSysCmdInitMeter, " ", 100)
varReturn = SysCmd(acSysCmdSetStatus, " ")
If the progress meter is already displayed when you set the text by calling the SysCmd method with the acSysCmdSetStatus action argument, the SysCmd method automatically removes the meter.
Call the SysCmd method with other actions to determine system information about Microsoft Access, including which version number of Microsoft Access is running, whether it is a run-time version, the location of the Microsoft Access executable file, the setting for the /profile argument specified in the command line, and the name of an .ini file associated with Microsoft Access.
Note Both general and customized settings for Microsoft Access are now stored in the Windows Registry, so you probably won't need an .ini file with your Microsoft Access application. The acSysCmdIniFile action argument exists for compatibility with earlier versions of Microsoft Access.
Call the SysCmd method with the acSysCmdGetObjectState action argument and the objecttype and objectname arguments to return the state of a specified database object. An object can be in one of four possible states: not open or nonexistent, open, new, or changed but not saved.
For example, if you are designing a wizard that inserts a new field in a table, you need to determine whether the structure of the table has been changed but not yet saved, so that you can save it before modifying its structure. You can check the value returned by the SysCmd method to determine the state of the table.
The SysCmd method with the acSysCmdGetObjectState action argument can return any combination of the following constants.
Constant State of database object Value
acObjStateOpen Open 1
acObjStateDirty Changed but not saved 2
acObjStateNew New 4
Note If the object referred to by the objectname argument is either not open or doesn't exist, the SysCmd method returns a value of zero.
SysCmd Method
You can use the SysCmd method to perform one of the following functions:
Display a progress meter or optional specified text in the status bar.
Return information about Microsoft Access and its associated files.
Return the state of a specified database object to indicate whether the object is open, is a new object, or has been changed but not saved.
For example, if you are building a custom wizard that creates a new form, you can use the SysCmd method to display a progress meter indicating the progress of your wizard as it constructs the form.
Syntax
ReturnValue = SysCmd(action[, text][, value])
ObjectState = SysCmd(action[, objecttype][, objectname])
The SysCmd method has the following arguments.
Argument Description
action One of the following intrinsic constants identifying the type of action to take.
The following set of constants applies to a progress meter. The SysCmd method returns a Null if these actions are successful. Otherwise, Microsoft Access generates a run-time error.
acSysCmdInitMeter Initializes the progress meter. You must specify the text and value arguments when you use this action.
acSysCmdUpdateMeter Updates the progress meter with the specified value. You must specify the text argument when you use this action.
acSysCmdRemoveMeter Removes the progress meter.
acSysCmdSetStatus Sets the status bar text to the text argument.
acSysCmdClearStatus Resets the status bar text.
The following set of constants provides information about Microsoft Access.
acSysCmdRuntime Returns True (–1) if a run-time version of Microsoft Access is running.
acSysCmdAccessVer Returns the version number of Microsoft Access.
acSysCmdIniFile Returns the name of the .ini file associated with Microsoft Access.
acSysCmdAccessDir Returns the name of the directory where Msaccess.exe is located.
acSysCmdProfile Returns the /profile setting specified by the user when starting Microsoft Access from the command line.
acSysCmdGetWorkgroupFile Returns the path to the workgroup file (System.mdw).
acSysCmdClearHelpTopic
The following constant provides information on the state of a database object.
acSysCmdGetObjectState Returns the state of the specified database object. You must specify the objecttype and objectname parts when you use this action value.
text A string expression identifying the text to be displayed left-aligned in the status bar. This argument is required when the action argument is acSysCmdInitMeter, acSysCmdUpdateMeter, or acSysCmdSetStatus; this argument isn't valid for other action argument values.
value A numeric expression that controls the display of the progress meter. This argument is required when the action argument is acSysCmdInitMeter; this argument isn't valid for other action argument values.
objecttype One of the following intrinsic constants:
acTable
acQuery
acForm
acReport
acMacro
acModule
acDataAccessPage
acDefault
acDiagram
acServerView
acStoreProcedure
This argument is required when the action argument is acSysCmdGetObjectState; this argument isn't valid for other action argument values.
objectname A string expression that is the valid name of a database object of the type specified by the objecttype argument. This argument is required when the action argument is acSysCmdGetObjectState; this argument isn't valid for other action argument values.
Remarks
By calling the SysCmd method with the various progress meter actions, you can display a progress meter in the status bar for an operation that has a known duration or number of steps, and update it to indicate the progress of the operation.
To display a progress meter in the status bar, you must first call the SysCmd method with the acSysCmdInitMeter action argument, and the text and value arguments. When the action argument is acSysCmdInitMeter, the value argument is the maximum value of the meter, or 100 percent.
To update the meter to show the progress of the operation, call the SysCmd method with the acSysCmdUpdateMeter action argument and the value argument. When the action argument is acSysCmdUpdateMeter, the SysCmd method uses the value argument to calculate the percentage displayed by the meter. For example, if you set the maximum value to 200 and then update the meter with a value of 100, the progress meter will be half-filled.
You can also change the text that's displayed in the status bar by calling the SysCmd method with the acSysCmdSetStatus action argument and the text argument. For example, during a sort you might change the text to "Sorting...". When the sort is complete, you would reset the status bar by removing the text. The text argument can contain approximately 80 characters. Because the status bar text is displayed by using a proportional font, the actual number of characters you can display is determined by the total width of all the characters specified by the text argument.
As you increase the width of the status bar text, you decrease the length of the meter. If the text is longer than the status bar and the action argument is acSysCmdInitMeter, the SysCmd method ignores the text and doesn't display anything in the status bar. If the text is longer than the status bar and the action argument is acSysCmdSetStatus, the SysCmd method truncates the text to fit the status bar.
You can't set the status bar text to a zero-length string (" "). If you want to remove the existing text from the status bar, set the text argument to a single space. The following examples illustrate ways to remove the text from the status bar:
varReturn = SysCmd(acSysCmdInitMeter, " ", 100)
varReturn = SysCmd(acSysCmdSetStatus, " ")
If the progress meter is already displayed when you set the text by calling the SysCmd method with the acSysCmdSetStatus action argument, the SysCmd method automatically removes the meter.
Call the SysCmd method with other actions to determine system information about Microsoft Access, including which version number of Microsoft Access is running, whether it is a run-time version, the location of the Microsoft Access executable file, the setting for the /profile argument specified in the command line, and the name of an .ini file associated with Microsoft Access.
Note Both general and customized settings for Microsoft Access are now stored in the Windows Registry, so you probably won't need an .ini file with your Microsoft Access application. The acSysCmdIniFile action argument exists for compatibility with earlier versions of Microsoft Access.
Call the SysCmd method with the acSysCmdGetObjectState action argument and the objecttype and objectname arguments to return the state of a specified database object. An object can be in one of four possible states: not open or nonexistent, open, new, or changed but not saved.
For example, if you are designing a wizard that inserts a new field in a table, you need to determine whether the structure of the table has been changed but not yet saved, so that you can save it before modifying its structure. You can check the value returned by the SysCmd method to determine the state of the table.
The SysCmd method with the acSysCmdGetObjectState action argument can return any combination of the following constants.
Constant State of database object Value
acObjStateOpen Open 1
acObjStateDirty Changed but not saved 2
acObjStateNew New 4
Note If the object referred to by the objectname argument is either not open or doesn't exist, the SysCmd method returns a value of zero.
Its undocumented and unsupported... so you will not find anything on it
anywhere.
anywhere.
Cyber Beber: I paid the points to see the show and I'm no more informed than I was before.
Sounds like the SysCmd function has a lot more power than what little MS gives us. Could you share where you got this little tidbit? I spent a long time learning about the RunCommand function and it is not documented by MS either. Neither is Decompile documented or supported. All of these things make our lives a little easier.
Do you have a list of other SysCmd actions that we might learn from?
Thanks,
Jim
Sounds like the SysCmd function has a lot more power than what little MS gives us. Could you share where you got this little tidbit? I spent a long time learning about the RunCommand function and it is not documented by MS either. Neither is Decompile documented or supported. All of these things make our lives a little easier.
Do you have a list of other SysCmd actions that we might learn from?
Thanks,
Jim
Dear Jim,
Unfortunately I cannot help you on that.
I got it from a guy that got it from his customer that got it as a very kept secret from Microsoft Support ...
You have my word, that if more treasures
like that will fall into my hands, I will not keep it for my self.
Beber.
Unfortunately I cannot help you on that.
I got it from a guy that got it from his customer that got it as a very kept secret from Microsoft Support ...
You have my word, that if more treasures
like that will fall into my hands, I will not keep it for my self.
Beber.
Jim , Have a look at
https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10288269
You might find it interesting.
https://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10288269
You might find it interesting.
Here is what the above is:
I'd like to create a program that I can run at the end of each day, that will compact all the databases our department uses, and copy them to a backup disk.
Is there code I can use to compact another database from my current database?
SendKeys "%tdc"
Will compact the current database, but can I use this for other databases somehow?
ssteeves
From Dedushka:
use something like this:
syscmd 602, "c:\MyFolder\MyDB.MDB", "c:\MyFolder\MyDB.MDB"
I'd like to create a program that I can run at the end of each day, that will compact all the databases our department uses, and copy them to a backup disk.
Is there code I can use to compact another database from my current database?
SendKeys "%tdc"
Will compact the current database, but can I use this for other databases somehow?
ssteeves
From Dedushka:
use something like this:
syscmd 602, "c:\MyFolder\MyDB.MDB", "c:\MyFolder\MyDB.MDB"
ASKER
you can compact the DB even from batch file using command line like this:
%access path%\msaccess.exe dbname /compact
%access path%\msaccess.exe dbname /compact
And I paid to see the show! Hmm! The constants are too close for my comfort. Can you imagine trying to compact your MDB and accidently enter 603 which turns it into an MDE losing all of your code?
I would like to see the rest of the unsupported list that he references.
Hey Jim: I don't think creating an .mde file (SysCmd 603) deletes the .mdb file. ????? Maybe it does, hell I didn't know about those neat numbers anyway!!! I don't think that I would be too concerned about creating the file, just all the nagging phone calls after being placed into production about "my database keeps growing and growing and I keep getting these weird ".mde" files on my computer.." Know what I mean?
Cheers, {]: ) Carey
Hey Jim: I don't think creating an .mde file (SysCmd 603) deletes the .mdb file. ????? Maybe it does, hell I didn't know about those neat numbers anyway!!! I don't think that I would be too concerned about creating the file, just all the nagging phone calls after being placed into production about "my database keeps growing and growing and I keep getting these weird ".mde" files on my computer.." Know what I mean?
Cheers, {]: ) Carey
I compact an MDB into the same MDB filename. So if I 603'd it then I would turn my test.mdb into a test.mdb which was actually an MDE.
I posted a new question asking for input from anyone who has found out about one of these secret functions. I've already put in 602, 603, and 504 - autocompile and save all modules.
These are something that I'd ever give a user but it would be a nice adjunct in trying to run a macro or SendKeys. Even an enhancement over RunCommand which I use all the time in my code.
Jim
I posted a new question asking for input from anyone who has found out about one of these secret functions. I've already put in 602, 603, and 504 - autocompile and save all modules.
These are something that I'd ever give a user but it would be a nice adjunct in trying to run a macro or SendKeys. Even an enhancement over RunCommand which I use all the time in my code.
Jim
I've opened a question in Access to find any information about using SysCmds. https://www.experts-exchange.com/Bin/Q.10288649
One of my programmers said that he has used SysCmd to change the shape of the cursor and to turn it on and off. Unfortunately, he couldn't remember the constants at the moment.
Jim
One of my programmers said that he has used SysCmd to change the shape of the cursor and to turn it on and off. Unfortunately, he couldn't remember the constants at the moment.
Jim
DoCmd.RunCommand acCmdMakeMDEFile
If what you mean is that you need to do this from VB 6.0, for instance, You could wrap the command above in a function, and reference the project name for the database, and call the wrapper.
I don't think you can do this straight from VB, without the help of the DB itself...
Brian