Opening another database with Access (VBA)

[dful]

I am trying to create an Access application that opens multiple Access applications.
I can get it to work on my home machine with:
Private Sub Command2_Click()
Dim stAppName As String

stAppName = "MSACCESS.EXE C:\2003\database4.mdb"
Call Shell(stAppName, 1)
End Sub

I can not get this code to work over my network @work.
I have tried:
stAppName = "MSACCESS.EXE \\company\companyfiles\2003\database4.mdb"
&
stAppName = "MSACCESS.EXE S:company\companyfiles\2003\database4.mdb"

The drive is mapped as "S"
this is a windows NT network.

Seems like it would be simple.
I keep getting a bunch of errors for invalid path.

Any ideas???

 

[SgtJarrow]

Try supplying the FULL path to MSACCESS.EXE....I have found this to be more effective.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[dful]

Is this what you mean?

Dim stAppName As String
stAppName = "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" \\company\companyfiles\2003\database4.mdb"

Is this a valid path?

 

[SgtJarrow]

yes....excpet you have an extra double quote in there...

Dim stAppName As String
stAppName = "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE \\company\companyfiles\2003\database4.mdb"

is correct.

****************************
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
MCSA, CNA, Net+, A+
w: rljohnso@stewart.com
h: wildmage@tampabay.rr.com

 

[dful]

Thanks.
I will give it a try.

 

[dful]

Still not working.
I used the following path:
stAppName = "C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE \\company\companyfiles\2003\database4.mdb"

This works on my home machine & my machine @work. It just doesn't work when I put the database on the network.

I am getting the following errors:
"The command line you used to start Microsoft Access contains an option that Microsoft Access doesn't recognize.
Exit and restart Microsoft Access using valid command line options."

I get this error 3 times.
Then I get the following error:
"Microsoft Access can't find the database file '\\company.mdb'
Make sure that you entered the correct path and file name."

Is there something wrong with my syntax?

Anymore ideas would be greatly appreciated?

 

[cmmrfrds]

Try putting single quotes around each name.

stAppName = "'C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE' '\\company\companyfiles\2003\database4.mdb'"

 

[Wocca]

The S: to refer to the drive where the DB is located dropped off a few messages back, shouldn't this be required ?

Regards
Warwick

 

[cmmrfrds]

Wocca, the S: refers to a mapped drive which is okay but less flexible that using the univeral naming convention of \\servername to refer directly to the server followed by the path name on the server.

 

[dful]

The single quotes do not work either.

Does it mean anything that the error:
"Microsoft Access can't find the database file '\\company.mdb'"

has company.mdb as the name of the file?
I thought that was odd.

I have tried similar code on other networks & it works fine.

Could it be because Access is not installed on thier network?

Other Ideas???

 

[RobCPA]

You may want to try to convert the long file name to the short DOS file name in order for the Shell function to work. The underlying DOS short name is the first six digits followed by ~ and a number. The short name is the result of DOS requiring a name no longer than 8 characters.

For example, your file name:
\\company\companyfiles\2003\database4.mdb

would actually have an equivalent DOS short name of:
\\company\compan~1\2003\databa~1.mdb

You could try doing a manual conversion like I did above. However, doing it manually can be a bit tricky, especially if you have similar file names over 8 characters in length. As an alternative, you can use an API function to do the conversion for you. You just pass the long name to the function and it returns the short name. Take a look at the following links:

http://www.mvps.org/access/api/api0020.htm

http://www.freevbcode.com/showcode.asp?id=506

I had seen a similar post last week and after working on it a bit over the last few days came up with this. It worked on my sample db, hope it works for you as well.

Rob

 

[cmmrfrds]

Is "company" the name of the server?

I am guessing it is looking for a server named company and not finding it, that is why it assumes company.mdb is the name of the app.

 

[dful]

I found what the problem is but I am not sure how to fix it.

the path is \\company\company files\2003\database4.mdb

The folder name is "Company files" with a space.
Is there a way to make this work?
I can not change the name of this folder.

 

[cmmrfrds]

A couple of things to try.

'\\company\company files\2003\database4.mdb'
\\company\[company files]\2003\database4.mdb
\\company\'company files'\2003\database4.mdb

 

[nice95gle]

mstrmage1768 was on the right track but the code was incomplete.

Option Compare Database
Private Declare Function GetShortPathName Lib "kernel32" _
Alias "GetShortPathNameA" (ByVal lpszLongPath As String, _
ByVal lpszShortPath As String, ByVal cchBuffer As Long) _
As Long
******************************************
Private Sub Command2_Click()

Dim stAppName As String
Dim lAns As Long
Dim sAns As String
Dim iLen As Integer
Dim FullPath As String
Dim AppToRun As String

FullPath = "\\company\company files\2003\database4.mdb"

sAns = Space(255)
lAns = GetShortPathName(FullPath, sAns, 255)
GetShortFileName = Left(sAns, lAns)

stAppName = "MSACCESS.EXE '" & GetShortFileName & "'"
AppToRun = Replace(stAppName, "'", " &quot
Call Shell(AppToRun, 1)

End Sub


THIS WILL WORK!!!
GOOD LUCK

 

[nice95gle]

Excuse me RobCPA was on the right track.

 

[dful]

Yes it does.

Thank You