TTom
Well-known Member
- Joined
- Jan 19, 2005
- Messages
- 518
I have one computer that refuses to connect from Excel to Access Database via SQL Query in VBA.
The only difference I can find is it runs Windows 7 Pro. It was connecting yesterday but not today.
Same happened the other day... it's not consistent. Driving my crazy!!
Gurus... Any thoughts on what is causing the bug?
The error on the Windows 7 cpu occurs when it reaches in the code: .Open stConn
Here is the background information and the code used:
Background:
1. Running Excel 2003 on several CPUs on server (all same version on Excel)
Ver# 11.8341.8341 (SP3)
2. Have Access 2003 database -- Same Ver# 11.8341.8341 (SP3) as Excel
3. Have following reference setting on all computers in Excel VBA References:
---a. Visual Basic For Applications
---b. Microsoft Excel 11.0 Object Library
---c. OLE Automation
---d. Microsoft Forms 2.0 Object Library
---e. Microsoft ActiveX Data Objects 2.8 Library
---f. Microsoft ActiveX Data Objects Recordset 2.8 Library
4. Folder security rights (read/write/list) are set the same on server for all.
5. Code below is in Excel's VBA and loads into memory array from MS Access database
6. One computer running Windows XP Pro, one running XP Home Ed, one other is
running Windows 7 Pro connected by group server (all Excel/Access files are on server.)
<code>
Public vaData() As Variant, j%, k%
Option Explicit
Sub Populate_WS()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB$, stConn$, stSQL$
Dim xlCalc As XlCalculation
Dim ws As Worksheet
Dim c%, r%
'In order to increase the performance.
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
'Instantiate the Connectionobject.
Set cnt = New ADODB.Connection
'Path to and the name of the database.
stDB = "M:\My Folder\MyDatabase.mdb"
'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"
'Create the SQL-statement.Nm_Common"
'stSQL = "Select * from MyData_Data order by FirstData"
stSQL = "Select FirstData, SecondData, ThirdData Updated from MyData_Data order by FirstData"
With cnt
.CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
.Open stConn 'Open connection.
'Instantiate the Recordsetobject and execute the SQL-state.
Set rst = .Execute(stSQL)
End With
With rst
Set .ActiveConnection = Nothing 'Disconnect the recordset.
k = .Fields.Count
j = .RecordCount
'Populate the array with the whole recordset.
vaData = .GetRows
'vaData(k,j) Query Array by field (db column) then record (db row)
End With
'Close the connection.
cnt.Close
'Restore the settings.
With Application
.Calculation = xlCalc
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing
End Sub
</code>
The only difference I can find is it runs Windows 7 Pro. It was connecting yesterday but not today.
Same happened the other day... it's not consistent. Driving my crazy!!
Gurus... Any thoughts on what is causing the bug?
The error on the Windows 7 cpu occurs when it reaches in the code: .Open stConn
Here is the background information and the code used:
Background:
1. Running Excel 2003 on several CPUs on server (all same version on Excel)
Ver# 11.8341.8341 (SP3)
2. Have Access 2003 database -- Same Ver# 11.8341.8341 (SP3) as Excel
3. Have following reference setting on all computers in Excel VBA References:
---a. Visual Basic For Applications
---b. Microsoft Excel 11.0 Object Library
---c. OLE Automation
---d. Microsoft Forms 2.0 Object Library
---e. Microsoft ActiveX Data Objects 2.8 Library
---f. Microsoft ActiveX Data Objects Recordset 2.8 Library
4. Folder security rights (read/write/list) are set the same on server for all.
5. Code below is in Excel's VBA and loads into memory array from MS Access database
6. One computer running Windows XP Pro, one running XP Home Ed, one other is
running Windows 7 Pro connected by group server (all Excel/Access files are on server.)
<code>
Public vaData() As Variant, j%, k%
Option Explicit
Sub Populate_WS()
Dim cnt As ADODB.Connection
Dim rst As ADODB.Recordset
Dim stDB$, stConn$, stSQL$
Dim xlCalc As XlCalculation
Dim ws As Worksheet
Dim c%, r%
'In order to increase the performance.
With Application
xlCalc = .Calculation
.Calculation = xlCalculationManual
.EnableEvents = False
.ScreenUpdating = False
End With
'Instantiate the Connectionobject.
Set cnt = New ADODB.Connection
'Path to and the name of the database.
stDB = "M:\My Folder\MyDatabase.mdb"
'Create the connectionstring.
stConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & stDB & ";"
'Create the SQL-statement.Nm_Common"
'stSQL = "Select * from MyData_Data order by FirstData"
stSQL = "Select FirstData, SecondData, ThirdData Updated from MyData_Data order by FirstData"
With cnt
.CursorLocation = adUseClient 'Necesary for creating disconnected recordset.
.Open stConn 'Open connection.
'Instantiate the Recordsetobject and execute the SQL-state.
Set rst = .Execute(stSQL)
End With
With rst
Set .ActiveConnection = Nothing 'Disconnect the recordset.
k = .Fields.Count
j = .RecordCount
'Populate the array with the whole recordset.
vaData = .GetRows
'vaData(k,j) Query Array by field (db column) then record (db row)
End With
'Close the connection.
cnt.Close
'Restore the settings.
With Application
.Calculation = xlCalc
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
'Release objects from memory.
Set rst = Nothing
Set cnt = Nothing
End Sub
</code>
