I have an application created in Excel that at certain points opens a
database named "engl.mdb". All worked fine for years until some of us got
new computers that now have VISTA as the OS. The database path is
c:\engl.mdb on the XP machines, but it can't be stored directly on the root
of C in Vista because Vista has some sort of security thing going on that
will only allow it to be opened in a READ-ONLY format. (I have searched for
days on how to change this but get nowhere.) So now we need to store the
database on the Vista machines in the following path --
c:\UserName\Engl\engl.mdb -- but on the XP machines the path is c:\engl.mdb

Is there code that can be substitued in the current Excel VBA code that can
call (Open) the database in either Vista or XP using the different path names?

Try looking at the value in Application.OperatingSystem We don't have
anything running Vista so I have no idea what it will say. XP returns
"Windows (32-bit) NT 5.01" Win 2K returns "Windows (32-bit) NT 5.00"

you can do something like:
if Application.OperatingSystem="Windows Vista" then
' open from c:\UserName\Engl\engl.mdb
else
' open from c:\engl.mdb
end if

"Donna" wrote:

I have an application created in Excel that at certain points opens a
database named "engl.mdb". All worked fine for years until some of us got
new computers that now have VISTA as the OS. The database path is
c:\engl.mdb on the XP machines, but it can't be stored directly on the root
of C in Vista because Vista has some sort of security thing going on that
will only allow it to be opened in a READ-ONLY format. (I have searched for
days on how to change this but get nowhere.) So now we need to store the
database on the Vista machines in the following path --
c:\UserName\Engl\engl.mdb -- but on the XP machines the path is c:\engl.mdb

Is there code that can be substitued in the current Excel VBA code that can
call (Open) the database in either Vista or XP using the different path names?

There's 2 problems however; major one being I didn't write the code. I know
a little about it & can get in there & change a few things, but as far as
knowing what you mean by "look at the value in Application.Operating System",
I'm not sure how to do that.

The other problem is that that path on Vista will change as the user changes
-- UserName portion of the path will be different depending on who is using
the program at the time.

"barnabel" wrote:

Try looking at the value in Application.OperatingSystem We don't have
anything running Vista so I have no idea what it will say. XP returns
"Windows (32-bit) NT 5.01" Win 2K returns "Windows (32-bit) NT 5.00"

you can do something like:
if Application.OperatingSystem="Windows Vista" then
' open from c:\UserName\Engl\engl.mdb
else
' open from c:\engl.mdb
end if

"Donna" wrote:

I have an application created in Excel that at certain points opens a
database named "engl.mdb". All worked fine for years until some of us got
new computers that now have VISTA as the OS. The database path is
c:\engl.mdb on the XP machines, but it can't be stored directly on the root
of C in Vista because Vista has some sort of security thing going on that
will only allow it to be opened in a READ-ONLY format. (I have searched for
days on how to change this but get nowhere.) So now we need to store the
database on the Vista machines in the following path --
c:\UserName\Engl\engl.mdb -- but on the XP machines the path is c:\engl.mdb

Is there code that can be substitued in the current Excel VBA code that can
call (Open) the database in either Vista or XP using the different path names?

This is an Excel group.

But here is a suggestion

On Error Resum Next
Open from "C:\engel.mdb"
Open from "C:\" & Environ("UserName") & "\=Eng\engel.mdb"
On Error goto 0

change to the correc t opening code.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"Donna" wrote in message
...
There's 2 problems however; major one being I didn't write the code. I
know
a little about it & can get in there & change a few things, but as far as
knowing what you mean by "look at the value in Application.Operating
System",
I'm not sure how to do that.

The other problem is that that path on Vista will change as the user
changes
-- UserName portion of the path will be different depending on who is
using
the program at the time.

"barnabel" wrote:

Try looking at the value in Application.OperatingSystem We don't have
anything running Vista so I have no idea what it will say. XP returns
"Windows (32-bit) NT 5.01" Win 2K returns "Windows (32-bit) NT 5.00"

you can do something like:
if Application.OperatingSystem="Windows Vista" then
' open from c:\UserName\Engl\engl.mdb
else
' open from c:\engl.mdb
end if

"Donna" wrote:

I have an application created in Excel that at certain points opens a
database named "engl.mdb". All worked fine for years until some of us
got
new computers that now have VISTA as the OS. The database path is
c:\engl.mdb on the XP machines, but it can't be stored directly on the
root
of C in Vista because Vista has some sort of security thing going on
that
will only allow it to be opened in a READ-ONLY format. (I have
searched for
days on how to change this but get nowhere.) So now we need to store
the
database on the Vista machines in the following path --
c:\UserName\Engl\engl.mdb -- but on the XP machines the path is
c:\engl.mdb

Is there code that can be substitued in the current Excel VBA code that
can
call (Open) the database in either Vista or XP using the different path
names?

Here is some current code:

Sub OpenAccess()
'THIS WORKS; IT ALSO REQURES THE SUB "SHOWACCESS" AND ASSOCIATED
'OPTION EXPLICIT FUNCTION AND CONST STATEMENTS AT TOP OF MODULE

Set objAccess = GetObject("c:\UOEnglish.mdb")
ShowAccess objAccess, SW_MAXIMIZE

'EXAMPLE OF USING WITH TO EXECUTE COMMANDS
'With objAccess
'.AutoCenter = True 'doesn't work...need to define form acts on?
'.Visible = True 'makes it visible
'.DoCmd.RunMacro "mcrImportCourses" 'runs this macro
' Print the Product Catalog report.
'.DoCmd.OpenReport "Catalog", acViewNormal, , _
' "CategoryName = '" & strCategoryName & "'"
'End With

'EXAMPLE OF ACCESS COMMAND TO CONTROL FORMS
'Forms!Customers.Visible = True

'objAccess.DoCmd.AutoCenter = True 'doesn't work
'objAccess.DoCmd.Forms!Switchboard.AutoCenter = True 'did not work

'EXECUTING COMMANDS WITH THE OBJECT ATTACHED TO EACH COMMAND
'objAccess.DoCmd.RunMacro "mcrImportCourses"
'objAccess.Quit 'closes Access
'Set objAccess = Nothing


End Sub
Private Sub UpdateAccessUDR()
'Having problems with this...need to debug
'To add named variables to records table in Access database
'
'Dim dbMain As ADODB.Connection
Dim AccTable 'The variable for Access Table for the records to be added to
'Dim dbMain As ADODB.Connection 'compile error: user-defined type not defined
Dim strSQL As String 'define SQL string
Dim EnrlCur, UDR 'variables for those values
Dim UDRColNum 'column number for the UDR data
Dim i As Integer
Dim TotRecNum As Integer

Sheets("Main Data").Select

FilenameTest = "UOEnglish.mdb"
DocPath = "c:\"
FnameTemp = DocPath & FilenameTest
AccTable = "tblCourses"

Dim dbMain As Object 'this seemed to be accepted
Set dbMain = CreateObject("ADODB.Connection")

'Dim rsRecordset As ADODB.Recordset 'this suggested fix didn't work

'open connection

'METHOD USING FnameTemp FOR FILENAME...should work
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FnameTemp & ";Persist Security Info=False"

'selects all records from table defined by AccTable variable
' check on method to select specific records that match fields with AND/OR
strSQL = "SELECT * FROM " & AccTable


'declare, set, open recordset, using above strSQL select command
' to get records from AccTable
Set rsRecordset = New ADODB.Recordset 'didn't like this...error
rsRecordset.Open strSQL, dbMain, adOpenDynamic, adLockOptimistic

'check for empty recordset
If rsRecordset.BOF And rsRecordset.EOF Then
'MsgBox "Recordset is empty!"
GoTo AddNewRecord
'Exit Sub
End If


"Donna" wrote:

I have an application created in Excel that at certain points opens a
database named "engl.mdb". All worked fine for years until some of us got
new computers that now have VISTA as the OS. The database path is
c:\engl.mdb on the XP machines, but it can't be stored directly on the root
of C in Vista because Vista has some sort of security thing going on that
will only allow it to be opened in a READ-ONLY format. (I have searched for
days on how to change this but get nowhere.) So now we need to store the
database on the Vista machines in the following path --
c:\UserName\Engl\engl.mdb -- but on the XP machines the path is c:\engl.mdb

Is there code that can be substitued in the current Excel VBA code that can
call (Open) the database in either Vista or XP using the different path names?

You can find the value for the Application.OperatingSystem by going into the
macro editor, pressing Ctrl-G to open the immediate window and typeing "Print
Application.OperatingSystem" and press enter.

That said Bob's way is fairly easy and you can replace the line:
Set objAccess = GetObject("c:\UOEnglish.mdb")
with:
objAccess=nothing
On Error Resume Next
Set objAccess = GetObject("c:\UOEnglish.mdb")
Set objAccess = GetObject("C:\" & Environ("UserName") & "\=Eng\engel.mdb")
On Error goto 0
if (ofjAccess < Nothing) then

Thanks, this is looking good but before I can try it I have one more question
- is there code that will work so that I don't have to type in each user's
name; i.e., is there something that works for the generic current user of the
computer? The path right now would be c:\Donna\Engl\engl.mdb. Is there some
code that would read c:\CurrentUser\Engl.mdb, etc, without having to add a
line for each person's name; Susan, Marilyn, Mike, blah blah blah. What
would the correct format be?

"barnabel" wrote:

You can find the value for the Application.OperatingSystem by going into the
macro editor, pressing Ctrl-G to open the immediate window and typeing "Print
Application.OperatingSystem" and press enter.

That said Bob's way is fairly easy and you can replace the line:
Set objAccess = GetObject("c:\UOEnglish.mdb")
with:
objAccess=nothing
On Error Resume Next
Set objAccess = GetObject("c:\UOEnglish.mdb")
Set objAccess = GetObject("C:\" & Environ("UserName") & "\=Eng\engel.mdb")
On Error goto 0
if (ofjAccess < Nothing) then
.
.
.
End If

One of the 2 statements will always generate an error and the other should
succeed. You should check that you found the file though.


"Donna" wrote:

Here is some current code:

Sub OpenAccess()
'THIS WORKS; IT ALSO REQURES THE SUB "SHOWACCESS" AND ASSOCIATED
'OPTION EXPLICIT FUNCTION AND CONST STATEMENTS AT TOP OF MODULE

Set objAccess = GetObject("c:\UOEnglish.mdb")
ShowAccess objAccess, SW_MAXIMIZE

'EXAMPLE OF USING WITH TO EXECUTE COMMANDS
'With objAccess
'.AutoCenter = True 'doesn't work...need to define form acts on?
'.Visible = True 'makes it visible
'.DoCmd.RunMacro "mcrImportCourses" 'runs this macro
' Print the Product Catalog report.
'.DoCmd.OpenReport "Catalog", acViewNormal, , _
' "CategoryName = '" & strCategoryName & "'"
'End With

'EXAMPLE OF ACCESS COMMAND TO CONTROL FORMS
'Forms!Customers.Visible = True

'objAccess.DoCmd.AutoCenter = True 'doesn't work
'objAccess.DoCmd.Forms!Switchboard.AutoCenter = True 'did not work

'EXECUTING COMMANDS WITH THE OBJECT ATTACHED TO EACH COMMAND
'objAccess.DoCmd.RunMacro "mcrImportCourses"
'objAccess.Quit 'closes Access
'Set objAccess = Nothing


End Sub
Private Sub UpdateAccessUDR()
'Having problems with this...need to debug
'To add named variables to records table in Access database
'
'Dim dbMain As ADODB.Connection
Dim AccTable 'The variable for Access Table for the records to be added to
'Dim dbMain As ADODB.Connection 'compile error: user-defined type not defined
Dim strSQL As String 'define SQL string
Dim EnrlCur, UDR 'variables for those values
Dim UDRColNum 'column number for the UDR data
Dim i As Integer
Dim TotRecNum As Integer

Sheets("Main Data").Select

FilenameTest = "UOEnglish.mdb"
DocPath = "c:\"
FnameTemp = DocPath & FilenameTest
AccTable = "tblCourses"

Dim dbMain As Object 'this seemed to be accepted
Set dbMain = CreateObject("ADODB.Connection")

'Dim rsRecordset As ADODB.Recordset 'this suggested fix didn't work

'open connection

'METHOD USING FnameTemp FOR FILENAME...should work
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FnameTemp & ";Persist Security Info=False"

'selects all records from table defined by AccTable variable
' check on method to select specific records that match fields with AND/OR
strSQL = "SELECT * FROM " & AccTable


'declare, set, open recordset, using above strSQL select command
' to get records from AccTable
Set rsRecordset = New ADODB.Recordset 'didn't like this...error
rsRecordset.Open strSQL, dbMain, adOpenDynamic, adLockOptimistic

'check for empty recordset
If rsRecordset.BOF And rsRecordset.EOF Then
'MsgBox "Recordset is empty!"
GoTo AddNewRecord
'Exit Sub
End If


"Donna" wrote:

I have an application created in Excel that at certain points opens a
database named "engl.mdb". All worked fine for years until some of us got
new computers that now have VISTA as the OS. The database path is
c:\engl.mdb on the XP machines, but it can't be stored directly on the root
of C in Vista because Vista has some sort of security thing going on that
will only allow it to be opened in a READ-ONLY format. (I have searched for
days on how to change this but get nowhere.) So now we need to store the
database on the Vista machines in the following path --
c:\UserName\Engl\engl.mdb -- but on the XP machines the path is c:\engl.mdb

Is there code that can be substitued in the current Excel VBA code that can
call (Open) the database in either Vista or XP using the different path names?

That is what the Environ("UserName") is doing. It is getting the value of
the system environment variable that has the user's name. If you open a
command window and type "set" and enter you can see all of the environment
variables and their current values.


"Donna" wrote:

Thanks, this is looking good but before I can try it I have one more question
- is there code that will work so that I don't have to type in each user's
name; i.e., is there something that works for the generic current user of the
computer? The path right now would be c:\Donna\Engl\engl.mdb. Is there some
code that would read c:\CurrentUser\Engl.mdb, etc, without having to add a
line for each person's name; Susan, Marilyn, Mike, blah blah blah. What
would the correct format be?

"barnabel" wrote:

You can find the value for the Application.OperatingSystem by going into the
macro editor, pressing Ctrl-G to open the immediate window and typeing "Print
Application.OperatingSystem" and press enter.

That said Bob's way is fairly easy and you can replace the line:
Set objAccess = GetObject("c:\UOEnglish.mdb")
with:
objAccess=nothing
On Error Resume Next
Set objAccess = GetObject("c:\UOEnglish.mdb")
Set objAccess = GetObject("C:\" & Environ("UserName") & "\=Eng\engel.mdb")
On Error goto 0
if (ofjAccess < Nothing) then
.
.
.
End If

One of the 2 statements will always generate an error and the other should
succeed. You should check that you found the file though.


"Donna" wrote:

Here is some current code:

Sub OpenAccess()
'THIS WORKS; IT ALSO REQURES THE SUB "SHOWACCESS" AND ASSOCIATED
'OPTION EXPLICIT FUNCTION AND CONST STATEMENTS AT TOP OF MODULE

Set objAccess = GetObject("c:\UOEnglish.mdb")
ShowAccess objAccess, SW_MAXIMIZE

'EXAMPLE OF USING WITH TO EXECUTE COMMANDS
'With objAccess
'.AutoCenter = True 'doesn't work...need to define form acts on?
'.Visible = True 'makes it visible
'.DoCmd.RunMacro "mcrImportCourses" 'runs this macro
' Print the Product Catalog report.
'.DoCmd.OpenReport "Catalog", acViewNormal, , _
' "CategoryName = '" & strCategoryName & "'"
'End With

'EXAMPLE OF ACCESS COMMAND TO CONTROL FORMS
'Forms!Customers.Visible = True

'objAccess.DoCmd.AutoCenter = True 'doesn't work
'objAccess.DoCmd.Forms!Switchboard.AutoCenter = True 'did not work

'EXECUTING COMMANDS WITH THE OBJECT ATTACHED TO EACH COMMAND
'objAccess.DoCmd.RunMacro "mcrImportCourses"
'objAccess.Quit 'closes Access
'Set objAccess = Nothing


End Sub
Private Sub UpdateAccessUDR()
'Having problems with this...need to debug
'To add named variables to records table in Access database
'
'Dim dbMain As ADODB.Connection
Dim AccTable 'The variable for Access Table for the records to be added to
'Dim dbMain As ADODB.Connection 'compile error: user-defined type not defined
Dim strSQL As String 'define SQL string
Dim EnrlCur, UDR 'variables for those values
Dim UDRColNum 'column number for the UDR data
Dim i As Integer
Dim TotRecNum As Integer

Sheets("Main Data").Select

FilenameTest = "UOEnglish.mdb"
DocPath = "c:\"
FnameTemp = DocPath & FilenameTest
AccTable = "tblCourses"

Dim dbMain As Object 'this seemed to be accepted
Set dbMain = CreateObject("ADODB.Connection")

'Dim rsRecordset As ADODB.Recordset 'this suggested fix didn't work

'open connection

'METHOD USING FnameTemp FOR FILENAME...should work
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FnameTemp & ";Persist Security Info=False"

'selects all records from table defined by AccTable variable
' check on method to select specific records that match fields with AND/OR
strSQL = "SELECT * FROM " & AccTable


'declare, set, open recordset, using above strSQL select command
' to get records from AccTable
Set rsRecordset = New ADODB.Recordset 'didn't like this...error
rsRecordset.Open strSQL, dbMain, adOpenDynamic, adLockOptimistic

'check for empty recordset
If rsRecordset.BOF And rsRecordset.EOF Then
'MsgBox "Recordset is empty!"
GoTo AddNewRecord
'Exit Sub
End If


"Donna" wrote:

I have an application created in Excel that at certain points opens a
database named "engl.mdb". All worked fine for years until some of us got
new computers that now have VISTA as the OS. The database path is
c:\engl.mdb on the XP machines, but it can't be stored directly on the root
of C in Vista because Vista has some sort of security thing going on that
will only allow it to be opened in a READ-ONLY format. (I have searched for
days on how to change this but get nowhere.) So now we need to store the
database on the Vista machines in the following path --
c:\UserName\Engl\engl.mdb -- but on the XP machines the path is c:\engl.mdb

Is there code that can be substitued in the current Excel VBA code that can
call (Open) the database in either Vista or XP using the different path names?

Oh, great! So I'll change "Donna" back to "UserName" in my code but I still
get the the compile error message "Invalid use of object".

And I have to mention how much I appreciate this group and all the help it
has provided. You guys are amazing!

"barnabel" wrote:

That is what the Environ("UserName") is doing. It is getting the value of
the system environment variable that has the user's name. If you open a
command window and type "set" and enter you can see all of the environment
variables and their current values.


"Donna" wrote:

Thanks, this is looking good but before I can try it I have one more question
- is there code that will work so that I don't have to type in each user's
name; i.e., is there something that works for the generic current user of the
computer? The path right now would be c:\Donna\Engl\engl.mdb. Is there some
code that would read c:\CurrentUser\Engl.mdb, etc, without having to add a
line for each person's name; Susan, Marilyn, Mike, blah blah blah. What
would the correct format be?

"barnabel" wrote:

You can find the value for the Application.OperatingSystem by going into the
macro editor, pressing Ctrl-G to open the immediate window and typeing "Print
Application.OperatingSystem" and press enter.

That said Bob's way is fairly easy and you can replace the line:
Set objAccess = GetObject("c:\UOEnglish.mdb")
with:
objAccess=nothing
On Error Resume Next
Set objAccess = GetObject("c:\UOEnglish.mdb")
Set objAccess = GetObject("C:\" & Environ("UserName") & "\=Eng\engel.mdb")
On Error goto 0
if (ofjAccess < Nothing) then
.
.
.
End If

One of the 2 statements will always generate an error and the other should
succeed. You should check that you found the file though.


"Donna" wrote:

Here is some current code:

Sub OpenAccess()
'THIS WORKS; IT ALSO REQURES THE SUB "SHOWACCESS" AND ASSOCIATED
'OPTION EXPLICIT FUNCTION AND CONST STATEMENTS AT TOP OF MODULE

Set objAccess = GetObject("c:\UOEnglish.mdb")
ShowAccess objAccess, SW_MAXIMIZE

'EXAMPLE OF USING WITH TO EXECUTE COMMANDS
'With objAccess
'.AutoCenter = True 'doesn't work...need to define form acts on?
'.Visible = True 'makes it visible
'.DoCmd.RunMacro "mcrImportCourses" 'runs this macro
' Print the Product Catalog report.
'.DoCmd.OpenReport "Catalog", acViewNormal, , _
' "CategoryName = '" & strCategoryName & "'"
'End With

'EXAMPLE OF ACCESS COMMAND TO CONTROL FORMS
'Forms!Customers.Visible = True

'objAccess.DoCmd.AutoCenter = True 'doesn't work
'objAccess.DoCmd.Forms!Switchboard.AutoCenter = True 'did not work

'EXECUTING COMMANDS WITH THE OBJECT ATTACHED TO EACH COMMAND
'objAccess.DoCmd.RunMacro "mcrImportCourses"
'objAccess.Quit 'closes Access
'Set objAccess = Nothing


End Sub
Private Sub UpdateAccessUDR()
'Having problems with this...need to debug
'To add named variables to records table in Access database
'
'Dim dbMain As ADODB.Connection
Dim AccTable 'The variable for Access Table for the records to be added to
'Dim dbMain As ADODB.Connection 'compile error: user-defined type not defined
Dim strSQL As String 'define SQL string
Dim EnrlCur, UDR 'variables for those values
Dim UDRColNum 'column number for the UDR data
Dim i As Integer
Dim TotRecNum As Integer

Sheets("Main Data").Select

FilenameTest = "UOEnglish.mdb"
DocPath = "c:\"
FnameTemp = DocPath & FilenameTest
AccTable = "tblCourses"

Dim dbMain As Object 'this seemed to be accepted
Set dbMain = CreateObject("ADODB.Connection")

'Dim rsRecordset As ADODB.Recordset 'this suggested fix didn't work

'open connection

'METHOD USING FnameTemp FOR FILENAME...should work
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FnameTemp & ";Persist Security Info=False"

'selects all records from table defined by AccTable variable
' check on method to select specific records that match fields with AND/OR
strSQL = "SELECT * FROM " & AccTable


'declare, set, open recordset, using above strSQL select command
' to get records from AccTable
Set rsRecordset = New ADODB.Recordset 'didn't like this...error
rsRecordset.Open strSQL, dbMain, adOpenDynamic, adLockOptimistic

'check for empty recordset
If rsRecordset.BOF And rsRecordset.EOF Then
'MsgBox "Recordset is empty!"
GoTo AddNewRecord
'Exit Sub
End If


"Donna" wrote:

I have an application created in Excel that at certain points opens a
database named "engl.mdb". All worked fine for years until some of us got
new computers that now have VISTA as the OS. The database path is
c:\engl.mdb on the XP machines, but it can't be stored directly on the root
of C in Vista because Vista has some sort of security thing going on that
will only allow it to be opened in a READ-ONLY format. (I have searched for
days on how to change this but get nowhere.) So now we need to store the
database on the Vista machines in the following path --
c:\UserName\Engl\engl.mdb -- but on the XP machines the path is c:\engl.mdb

Is there code that can be substitued in the current Excel VBA code that can
call (Open) the database in either Vista or XP using the different path names?

Not sure if this got sent so I'm sending again. Getting 404 error when I
tried to post.

You can find the value for the Application.OperatingSystem by going into the
macro editor, pressing Ctrl-G to open the immediate window and typeing "Print
Application.OperatingSystem" and press enter.

That said Bob's way is fairly easy and you can replace the line:
Set objAccess = GetObject("c:\UOEnglish.mdb")
with:
objAccess=nothing
On Error Resume Next
Set objAccess = GetObject("c:\UOEnglish.mdb")
Set objAccess = GetObject("C:\" & Environ("UserName") & "\=Eng\engel.mdb")
On Error goto 0
if (ofjAccess < Nothing) then

Okay, here's my code - hopefully I understood what you were talking about,
but I get an error message that highlights the word NOTHING in the "If"
statement and says "Compile Error - invalid use of object." Any ideas?

Sub OpenAccess()

objAccess = Nothing
On Error Resume Next
Set objAccess = GetObject("c:\UOEnglish.mdb")
Set objAccess = GetObject("c:\" & Environ("Donna") &
"\=EnglishDB\UOEnglish.mdb")
On Error GoTo 0

If (objAccess < Nothing) Then

ShowAccess objAccess, SW_MAXIMIZE

End If


"barnabel" wrote:

Not sure if this got sent so I'm sending again. Getting 404 error when I
tried to post.

You can find the value for the Application.OperatingSystem by going into the
macro editor, pressing Ctrl-G to open the immediate window and typeing "Print
Application.OperatingSystem" and press enter.

That said Bob's way is fairly easy and you can replace the line:
Set objAccess = GetObject("c:\UOEnglish.mdb")
with:
objAccess=nothing
On Error Resume Next
Set objAccess = GetObject("c:\UOEnglish.mdb")
Set objAccess = GetObject("C:\" & Environ("UserName") & "\=Eng\engel.mdb")
On Error goto 0
if (ofjAccess < Nothing) then
.
.
.
End If

One of the 2 statements will always generate an error and the other should
succeed. You should check that you found the file though.


"Donna" wrote:

Here is some current code:

Sub OpenAccess()
'THIS WORKS; IT ALSO REQURES THE SUB "SHOWACCESS" AND ASSOCIATED
'OPTION EXPLICIT FUNCTION AND CONST STATEMENTS AT TOP OF MODULE

Set objAccess = GetObject("c:\UOEnglish.mdb")
ShowAccess objAccess, SW_MAXIMIZE

'EXAMPLE OF USING WITH TO EXECUTE COMMANDS
'With objAccess
'.AutoCenter = True 'doesn't work...need to define form acts on?
'.Visible = True 'makes it visible
'.DoCmd.RunMacro "mcrImportCourses" 'runs this macro
' Print the Product Catalog report.
'.DoCmd.OpenReport "Catalog", acViewNormal, , _
' "CategoryName = '" & strCategoryName & "'"
'End With

'EXAMPLE OF ACCESS COMMAND TO CONTROL FORMS
'Forms!Customers.Visible = True

'objAccess.DoCmd.AutoCenter = True 'doesn't work
'objAccess.DoCmd.Forms!Switchboard.AutoCenter = True 'did not work

'EXECUTING COMMANDS WITH THE OBJECT ATTACHED TO EACH COMMAND
'objAccess.DoCmd.RunMacro "mcrImportCourses"
'objAccess.Quit 'closes Access
'Set objAccess = Nothing


End Sub
Private Sub UpdateAccessUDR()
'Having problems with this...need to debug
'To add named variables to records table in Access database
'
'Dim dbMain As ADODB.Connection
Dim AccTable 'The variable for Access Table for the records to be added to
'Dim dbMain As ADODB.Connection 'compile error: user-defined type not defined
Dim strSQL As String 'define SQL string
Dim EnrlCur, UDR 'variables for those values
Dim UDRColNum 'column number for the UDR data
Dim i As Integer
Dim TotRecNum As Integer

Sheets("Main Data").Select

FilenameTest = "UOEnglish.mdb"
DocPath = "c:\"
FnameTemp = DocPath & FilenameTest
AccTable = "tblCourses"

Dim dbMain As Object 'this seemed to be accepted
Set dbMain = CreateObject("ADODB.Connection")

'Dim rsRecordset As ADODB.Recordset 'this suggested fix didn't work

'open connection

'METHOD USING FnameTemp FOR FILENAME...should work
dbMain.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & FnameTemp & ";Persist Security Info=False"

'selects all records from table defined by AccTable variable
' check on method to select specific records that match fields with AND/OR
strSQL = "SELECT * FROM " & AccTable


'declare, set, open recordset, using above strSQL select command
' to get records from AccTable
Set rsRecordset = New ADODB.Recordset 'didn't like this...error
rsRecordset.Open strSQL, dbMain, adOpenDynamic, adLockOptimistic

'check for empty recordset
If rsRecordset.BOF And rsRecordset.EOF Then
'MsgBox "Recordset is empty!"
GoTo AddNewRecord
'Exit Sub
End If


"Donna" wrote:

I have an application created in Excel that at certain points opens a
database named "engl.mdb". All worked fine for years until some of us got
new computers that now have VISTA as the OS. The database path is
c:\engl.mdb on the XP machines, but it can't be stored directly on the root
of C in Vista because Vista has some sort of security thing going on that
will only allow it to be opened in a READ-ONLY format. (I have searched for
days on how to change this but get nowhere.) So now we need to store the
database on the Vista machines in the following path --
c:\UserName\Engl\engl.mdb -- but on the XP machines the path is c:\engl.mdb

Is there code that can be substitued in the current Excel VBA code that can
call (Open) the database in either Vista or XP using the different path names?