How can I OPEN a .mdb file

[AccessGuruCarl]

How can open the .mdb!!! after creating the .exe file

Shell "msaccess.exe" "D:\test\test.mdb"
*** doesn't work at all

Shell "C:\Program Files\Microsoft Office\Office10\msaccess.exe" "D:\test\test.mdb"
** this works in the vb form,
** but not in the Test.exe file consistantly......

Is there another way to open this?

Here is the code I'm using:
Dim myApp As String
Dim myMDB As String

myApp = "C:\Program Files\Microsoft Office\Office10\msaccess.exe"
myMDB = CurDir & "\EDI_Pro.mdb"

myApp = dblQts & myApp & dblQts & " " & dblQts & myMDB & dblQts
Debug.Print myApp

Shell myApp, vbMaximizedFocus




AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[gmmastros]

Try...

Shell "cmd /c msaccess.exe ""D:\test\test.mdb"""

Those extra quotes are there to accomodate spaces in the path and/or database file name.


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[RonRepp]

Although George is correct, I prefer to use Access as an object. If you really want to open the mdb, try this.

1) set a reference to MSAcess

Dim msaccess As Access.Application
Set msaccess = New Access.Application
msaccess.OpenCurrentDatabase Filepath, False, password
'Filepath=path of mdb
'False=Exclusive when opened (optional w/False being default)
'Password=Access password to open mdb (Optional w/"" being default)

I hope this helps.



Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[BobRodes]

Well. I prefer to open access files using ADO whenever possible, of course.

The OP isn't telling us why he's opening the Access file. if it's just to pull data, well....

 

[AccessGuruCarl]

Thanks for the replies,

Here's whats going on, in a little detail.

The .mdb file is basically a stand-alone application using its own forms and reports that builds a wire harness 'build list' based on user selected parts.

The .mdb file...
The parts list tables are updated consistantly, with minor changes to forms and reports. This file is sent to several customers, and it's easier to replace the entire db, they can't save records or edit, they simply use it to build the reports needed for the project.

From within the .mdb file the user presses [F12] key to open the Test.exe file to check for updates.

The test.exe file downloads the new updates via the web, then checks to see if the .mdb file is open. If it is, it closes it so that it can be replaced with the newer .mdb, then reopens it.

This seems to be where my error is occurring, If the .mdb file isn't open(I use short-cut in startmenu to run update), it works great. But if I have to close the file before I replace it, an occassional error... Can't locate .mdb.... It's defaulting to C:\Documents and Settings\Carl\My Documents\Harness.mdb

I'll try the suggestions above.
In VB, if I set a reference to Access will the .exe file remember this, or do I need to have a function to check that the reference is there?
** All users must have Access to run the .mdb's so I'm hoping I don't need to reference it in code.

Thanks again,

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[JerryKlmns]

'No need set a reference to MSAcess like this
Dim objAccess As Object
On Error Resume Next
Err.Clear
'Grab the access
Set objAccess = GetObject(,"Access.Application")
Select Case Err.Number
   Case 429 'Access is not running
      Set objAccess = CreateObject("Access.Application")
      objAccess.OpenCurrentDatabase myMDB, False
      objAccess.Visible = True
   Case 0    'Access is running
      objAccess.Quit
      Set objAccess = Nothing
      Set objAccess = CreateObject("Access.Application")
      objAccess.OpenCurrentDatabase myMDB, False
      objAccess.Visible = True
   Case Else 'You have an error here
       MsgBox Err.Number & vbCrLf & Err.Description
End Select

And a question, is there a way to use this, on a mdb with user level security set?

The only way I can think of, would be to create a shortcut on the fly, suppling the UserId and Password, shell that and delete the shortcut. Any other thoughts?

 

[RonRepp]

It seems that we all have our favorites.

Bob: Actually, I agree with the ADO part, but only for retrieving data and creating tables/views. This is probably because I don't know HOW to open an mdb using ADO...at least not in the sense that I thought AccessGuruCarl was attempting.

Jerry: The reason I don't use the createobject for Access and other MS products is because I'm addicted to Intellisense.

Carl: Perhaps if you posted some of your code, we could better help you.

Good luck.


Ron Repp

If gray hair is a sign of wisdom, then I'm a genius.

 

[BobRodes]

Considerably more concise, and accomplishing the same thing, is

'No need set a reference to MSAcess like this
Dim objAccess As Object
'Grab the access
Set objAccess = GetObject("myPath\myDB.mdb")
objAccess.Visible = True

Jerry and Carl, the point is that GetObject doesn't care whether there's an existing instance of Access open if you reference a filename in the first argument. This is analogous to double clicking on the .mdb file from, say, the explorer. If you have an existing instance of Access, the system will use it to contain the mdb file, if not, the system will open a new one.

It's also important to keep in mind that once objAccess goes out of scope here the application will automatically quit. The rules on this are inconsistent (for example, IE instances will remain), but that's what Access does. So, you'll need to make sure that your object variable is module-level at least.

One more thing: if you're going to use a variable of type Object, so you don't have to reference the variable, you'll want to investigate late binding. Keep in mind that doing this does NOT obviate the target machine from having the Access library in place. However, since the app requires the Access exe to be in place, the Access object library is also in place unless someone goes out of their way to mess with it, such as renaming msacc.olb.

 

[tsh73]

so, if I understand
your VB program closes Acess (if it's still open)
then re-writes MDB to new one
then opens it.
And sometimes fail on open.

1) I guess double clicking MDB always open it, So probably you should do same in you VB code - using shell or shellExecute on MDB file, no mention Acess EXE at all.
And no using any access objects (only if you have reasons to do that you did not tell yet?)
2) could it fail open just for timing reason - the OS not finished writing? See if some sleep() would help it.

 

[SBerthold]

>It's defaulting to C:\Documents and Settings\Carl\My Documents\Harness.mdb

If I recall right, if the file isn't found, Access will default to the path which is set in Access under Options, or something like that.


>but not in the Test.exe file consistantly......

I would also think that your problem is caused by something slowing down the actual saving process - probably the AV program or some backup service, or the download alone, or just a lot going on, especially if the file is large.

"Sleep", as mentioned, alone may help, but no telling how long to sleep.

How about using sleep in a Do...Loop, do a file check (API, FSO or Dir), then Sleep for a second or two.

You could also add a time-out in there, (and maybe use a HotKey, or a dialog, using the latter two require having DoEvents in the loop, so the user can also end the process), so that in case something goes wrong, the programm will quit.

 

[BobRodes]

<no telling how long to sleep.

Check the last post here: thread222-1285729. Should give you the means to kill the Access process and restart it.

HTH

Bob

 

[SBerthold]

I meant more , (as it is how I understood it), as how to find out when the downloaded file is actually available for use, and not trying to open the mdb before it is even available. I may have mis-understood the problem:

>** this works in the vb form,
>** but not in the Test.exe file consistantly......

and

> But if I have to close the file before I replace it, an occassional error... Can't locate .mdb.... It's defaulting to C:\Documents and Settings\Carl\My Documents\Harness.mdb

 

[BobRodes]

Well, that occasional error is probably because the file hasn't been copied out before it gets accessed. So, you get the handle of the object that you're using to close the file, run isprocessrunning against it, and only access the mdb file after it exits the loop.

Bob

 

[AccessGuruCarl]

Thanks everyone for the input....

Problem is solved....

I needed to pass the path&filename to a function that renames the path & filename to Short filenames. The actual .mdb file has an underscore in it's name and I guess this was throwing off access. Since I changed the path to a shortfile name, everything is working great.

Thanks again....

AccessGuruCarl
Programmers helping programmers
you can't find a better site.