help with MDE (A2K)

Topic

New Reply

Hello,
I was wondering if there was a way to use code to automate the creation of an MDE with a command button. I already have it so OnClick opens a Save Dialog box to the correct directory in which to save the file. Could it just be forced by putting .mde at the end of the chosen file name? I have a feeling it wouldn’t be that simple.

Thanks,
Sarah

Reply | Quote

Replies

Unfortunately, I don’t think so – I presume you are using either a macro or a module to execute the menu command that saves your current database in an MDE format. If that’s the case, the dialog box is popped up by the menu command, and there’s no way to turn that off, and simply tell Access to save it. (If you are doing it in some other way, please post back with the details.)

Just out of curiosity, why do you want to automate the process? It’s not something that we do frequently enough to be concerned about the small savings in time.

Reply | Quote

Thanks for your reply Wendell,

Here is the code I have so far for the OnClick event. I used it in other parts of my database to Open Files through Shell commands. I was trying to modify it to be used for Save Files (i.e. I read somewhere to change -1 to 0):

Private Sub cmdLoad_Click()

‘Dim cdlg As New CommonDialogAPI
‘Dim lngFormName As Long
‘Dim lngAppInstance As Long
Dim strInitDir As String
Dim strFileFilter As String
‘Dim lngResult As Long
‘
‘lngFormName = Me.hwnd
‘lngAppInstance = Application.hWndAccessApp
strInitDir = “P:GROUP-TRANSPORTATIONDOCUMENTSTIPsTIP_Database_04_to_Present”
strFileFilter = “Access Databases (*.mdb, *.mde)& *.mdb; *.mde”
‘
‘lngResult = cdlg.OpenFileDialog(lngFormName, lngAppInstance, strInitDir, strFileFilter)
Dim strFileName As String

strFileName = OpenCommDlg(“Access Databases”, “*.mde” & “.mdb”, 0, strInitDir, strFileFilter)

End Sub
————————————-
I just tried this code and I know I’m missing something because it won’t even save as an .mdb…but that’s what I have…

Why I want to automate this process…I know how to make the mde through the normal route, but there’s no guarantee that the person doing this job after me will. In fact the guarantee slides more towards the fact that they will know less about Access than I. And I’m just trying to make this little db as intuitive as possible, to take the edge off when I tell them this afternoon I’m moving to California , and so I can say hey it’s as easy as pushing this button. The database resides right now on my personal drive on the server but the bosses want a copy on the network for everyone to see. Oh and they too know less about Access than I.

Anyway, I hope that answered your questions.

Sarah

Reply | Quote

I’m afraid your code won’t work in any event, as I believe at best you would only be able to save the database with a .mde extension, but it would still be a full .mdb database where changes could be made to code, forms, reports, etc. If the rest of the users are all at novice level, I think I would suggest leaving the database as an MDB file to begin with. If there isn’t any there capable of making design changes to begin with, there shouldn’t be much in the way of changes! Either that, or suggest they find a local consultant if they want to make a design change down the road.

Reply | Quote

Thanks Wendell,
I wasn’t worried so much about design changes than about accidents happening if someone wandered into the db.

But in any rate, how could I make the code I posted work to save it as an .mdb? The db is saved on my drive as TIP_template. I wanted to open the dialog and enter a new name like TIP2004 and have it save in a different place on the server. As of right now that code doesn’t do anything but open the dialog box.

Thanks for all help.

Sarah

Reply | Quote

The only way I found to automate the creation of MDE’s was using an external automation tool, in my case AutoIt, to “drive” Access for me.
I added a registry entry to the MDB shell, which calls a VB script, which amongst other things creates the MDE (and makes an Access 2000 + Access 97 version, then zips them up)

Reply | Quote

As long as you don’t want to make an MDE out of it, you could simply use a file copy batch script in Windows to copy the file. Another alternative would be to create an MDE on your your drive, and then a batch script to copy it from your drive to the server drive. There is a TransferDatabase command that can be used to copy objects from one database to another, and with a good deal of complex code you could probably create a new .MDB format database that way, but the file copy is much easier. As Steve notes in his comment, you would probably need an external tool to actually create a new MDE from a MDB file.

One thing that should be a bit of a worry is the Compact/Repair process. That should be run regularly – say weekly – and is another thing that is difficult to do in the current database.

Reply | Quote

I found a “FileSystemObject.CopyFile” in the help files of Access is that a “file copy batch script?” Even if it is, I couldn’t get it to work…do you have a link to where I could get the code?

Also, I do have it set up to compact and repair on close, should I do something else?

Thanks Wendell, and Steve too!

Reply | Quote

Not sure why you couldn’t make it work – it needs to be in a VBA procedure invoked perhaps at the click of a button. The help file lays out things pretty clearly – one possibility is that you aren’t dealing with mapped network drives. Other than that ? ? ? ?

Yes, your compact and repair on close should be fine in nearly all cases. The only worry is if someone crashes their computer and actually corrupts the database, and in that case Access will prompt the user and ask if it can repair the database.

Reply | Quote

Sarah,
the FileSystemObject is a Microsoft object in the scripting DLL, scrrun.dll
It’s a general purpose object for dealing with, wait for it …., file system objects, so we’re talking about directories, oops sorry old name, folders and files. You can instantiate it using most languages, so VB, VBA or VB script can call create a FileSystemObject and then use the objects methods, e.g. as you’ve found there is a method to CopyFile. I use it a lot for bits and bobs with VB scripts

Lots on MSDN about it e.g. http://msdn.microsoft.com/library/default….ObjectModel.asp[/url]

Reply | Quote

Great joy and wonder…it’s almost working…thanks for the link to the msdn article! I set the references to the scrrun.dll and now the fso.copyfile is working great! Well, just one more thing. It’s copying my file into the right directory, but how can I get it to copy the file and rename it according to what the user types in the Save dialog? Right now it just names it the same old name no matter what I type.

Thanks Steve and Wendell for all your help.

Sarah

Reply | Quote

Glad to hear things are moving forward Sarah
– wish I could say the same on this side of the Atlantic
I don’t think the FSO has a RenameFile method, so to rename files I use the MoveFile method on the file in question

Scripting Runtime Library
MoveFile Method
Moves one or more files from one location to another.

object.MoveFile ( source, destination );
Arguments
object
Required. Always the name of a FileSystemObject.
source
Required. The path to the file or files to be moved. The source argument string can contain wildcard characters in the last path component only.
destination
Required. The path where the file or files are to be moved. The destination argument can’t contain wildcard characters.
Remarks
If source contains wildcards or destination ends with a path separator (), it is assumed that destination specifies an existing folder in which to move the matching files. Otherwise, destination is assumed to be the name of a destination file to create. In either case, three things can happen when an individual file is moved:

If destination does not exist, the file gets moved. This is the usual case.
If destination is an existing file, an error occurs.
If destination is a directory, an error occurs.
An error also occurs if a wildcard character that is used in source doesn’t match any files. The MoveFile method stops on the first error it encounters. No attempt is made to roll back any changes made before the error occurs.

Note This method allows moving files between volumes only if supported by the operating system.
The following example illustrates the use of the MoveFile method:

[VBScript]
Sub MoveAFile(Drivespec)
Dim fso
Set fso = CreateObject(“Scripting.FileSystemObject”)
fso.MoveFile Drivespec, “c:windowsdesktop”
End Sub
See Also
CopyFile Method | DeleteFile Method | GetFile Method | GetFileName Method | Move Method | MoveFolder Method | OpenTextFile Method
Applies To: FileSystemObject Object

——————————————————————————–

Reply | Quote

Thanks Steve,
Now I’m even more confused. Upon trying the movefile I got “Run Time Error 70: Permission Denied.”

But even so, I’m not sure how I see how I can rename the file through the save as Dialog box.

Sarah

Reply | Quote

Ah Ha!!! I can use the fso.CopyFile method, just instead of putting the file path as the destination, I put strFileName…saves it with the new file name and everything.

Thanks for all help!
Sarah

Reply | Quote