OZGOD
07-31-2009, 07:58 AM
G'day all, first post here. I'm hoping you guys can help me!
I have an Excel model that sends and receives information to an MS Access (all 2003 versions) database that is stored in a shared network location (network location is global read-write access and I have tested this).
Basically the way it works is that I have a macro that automatically runs when the Excel file is opened, that queries the Access DB and sends data back to the Excel file to update some sheets.
It works fine when anyone in my LOCAL site (ie on the site and network where my Access DB is physically located), even if the user does not have MS Access installed. But when remote users try to use it I come up with the following error. It's a run time error run time error 2147467259 80004005 saying "Microsoft JET Engine cannot open \\Natfile02\depts\Global Share Folder\Rev@Risk.mdb. It is already exclusively opened by another user, or you will need permission to view its data".
Anyone know what this means and how to fix it? I've tried everything, including:
1. making sure the database is in a folder with global read write access (i have tested this and they can physically navigate to the folder and make changes to files within the folder)
2. asking them to map the drive and folder location to make sure it shows up in their My Computer
3. making sure nobody else is in the file
My code is below - highlighted with a <--------- is where the Debug stops and where the error is (or just before this).
Again, the issue is ONLY happening when remote users try to use the model.
FYI I am using Microsoft Windows XP Professional 2002 SP3.
Appreciate any help. I've scoured the internet but haven't been able to find a solution.
Private Sub Workbook_Open()
'Makes cursor go to cell A8 on Input Data sheet
Sheets("Input Data").Select
Range("A8").Select
MsgBox "Master Data will now be updated.
This process will take 15-20 seconds. Please do not interrupt until complete.
A message box will appear to indicate it is complete. Click OK to start"
'This sub updates the master component and UPN lists
Dim dbsConn As ADODB.Connection
Set dbsConn = New ADODB.Connection
Dim connString As String
Dim filepath As String
Dim sql As String
filepath = "\\NATFILE02\DEPTS\Strategic Sourcing\Global Share Folder\RevAtRiskDB.mdb"
sql = "SELECT * FROM updatecomplist "
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath
dbsConn.Open connString <-----------------
'Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
ActiveWorkbook.Sheets(9).Activate
Range("A2").Select
ActiveSheet.Unprotect Password:="risk"
Range("A2:b65536").Value = Empty
rst.Open sql, dbsConn
row = 2
Do Until rst.EOF
Range("A" & row).Value = rst.Fields("Component").Value
Range("B" & row).Value = rst.Fields("Mfg Plant").Value
Range("C" & row).Value = rst.Fields("Join").Value
rst.MoveNext
row = row + 1
Loop
dbsConn.Close
Set dbsConn = New ADODB.Connection
sql = "SELECT * FROM updateupnlist "
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath
dbsConn.Open connString
'Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
ActiveWorkbook.Sheets(10).Activate
Range("A2").Select
ActiveSheet.Unprotect Password:="risk"
Range("A2:a65536").Value = Empty
rst.Open sql, dbsConn
row = 2
Do Until rst.EOF
Range("A" & row).Value = rst.Fields("UPN").Value
rst.MoveNext
row = row + 1
Loop
dbsConn.Close
Range("A2").Select
ActiveWorkbook.Sheets(2).Activate
Range("A8").Select
'Makes cursor go to cell A8 on Input Data sheet
Sheets("Input Data").Select
Range("A8").Select
MsgBox "Master Data update complete"
End Sub
I have an Excel model that sends and receives information to an MS Access (all 2003 versions) database that is stored in a shared network location (network location is global read-write access and I have tested this).
Basically the way it works is that I have a macro that automatically runs when the Excel file is opened, that queries the Access DB and sends data back to the Excel file to update some sheets.
It works fine when anyone in my LOCAL site (ie on the site and network where my Access DB is physically located), even if the user does not have MS Access installed. But when remote users try to use it I come up with the following error. It's a run time error run time error 2147467259 80004005 saying "Microsoft JET Engine cannot open \\Natfile02\depts\Global Share Folder\Rev@Risk.mdb. It is already exclusively opened by another user, or you will need permission to view its data".
Anyone know what this means and how to fix it? I've tried everything, including:
1. making sure the database is in a folder with global read write access (i have tested this and they can physically navigate to the folder and make changes to files within the folder)
2. asking them to map the drive and folder location to make sure it shows up in their My Computer
3. making sure nobody else is in the file
My code is below - highlighted with a <--------- is where the Debug stops and where the error is (or just before this).
Again, the issue is ONLY happening when remote users try to use the model.
FYI I am using Microsoft Windows XP Professional 2002 SP3.
Appreciate any help. I've scoured the internet but haven't been able to find a solution.
Private Sub Workbook_Open()
'Makes cursor go to cell A8 on Input Data sheet
Sheets("Input Data").Select
Range("A8").Select
MsgBox "Master Data will now be updated.
This process will take 15-20 seconds. Please do not interrupt until complete.
A message box will appear to indicate it is complete. Click OK to start"
'This sub updates the master component and UPN lists
Dim dbsConn As ADODB.Connection
Set dbsConn = New ADODB.Connection
Dim connString As String
Dim filepath As String
Dim sql As String
filepath = "\\NATFILE02\DEPTS\Strategic Sourcing\Global Share Folder\RevAtRiskDB.mdb"
sql = "SELECT * FROM updatecomplist "
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath
dbsConn.Open connString <-----------------
'Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
ActiveWorkbook.Sheets(9).Activate
Range("A2").Select
ActiveSheet.Unprotect Password:="risk"
Range("A2:b65536").Value = Empty
rst.Open sql, dbsConn
row = 2
Do Until rst.EOF
Range("A" & row).Value = rst.Fields("Component").Value
Range("B" & row).Value = rst.Fields("Mfg Plant").Value
Range("C" & row).Value = rst.Fields("Join").Value
rst.MoveNext
row = row + 1
Loop
dbsConn.Close
Set dbsConn = New ADODB.Connection
sql = "SELECT * FROM updateupnlist "
connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath
dbsConn.Open connString
'Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
ActiveWorkbook.Sheets(10).Activate
Range("A2").Select
ActiveSheet.Unprotect Password:="risk"
Range("A2:a65536").Value = Empty
rst.Open sql, dbsConn
row = 2
Do Until rst.EOF
Range("A" & row).Value = rst.Fields("UPN").Value
rst.MoveNext
row = row + 1
Loop
dbsConn.Close
Range("A2").Select
ActiveWorkbook.Sheets(2).Activate
Range("A8").Select
'Makes cursor go to cell A8 on Input Data sheet
Sheets("Input Data").Select
Range("A8").Select
MsgBox "Master Data update complete"
End Sub