I'm using VB to extract data from an Access database and create a spreadsheet using that data. I wrote the code and tried to run it. It starts to extract the data, but about half way through, it stalls. I have to press Ctrl+Alt+Del to quit out of it, and it stalls all my other programs that I'm running, and I always have to restart.
I have a feeling that the code I wrote hogs a lot of the resources which causes all the other programs to stall, but I don't know why it hangs in the first place. The database has around 20,000 to be exported to Excel, but it should be able to handle that right?
The code below is what I use to open the connection to Access and Excel. If there's a more efficient way of opening the connections please let me know. I also create recordsets using sql statements to filter the data i want to transfer.
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Excel.Application
Dim wkBook As Excel.Workbook
Dim wkSheet As Excel.Worksheet
cnn.Mode = adModeRead 'open the db in readonly mode
cnn.Mode = adModeShareExclusive 'open the db exclusively
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";"
Set xlApp = New Excel.Application 'create new hidden instance of Excel
Set rst = cnn.Execute("SELECT FIELD1, FIELD2 FROM TABLE1"
'example of a recordset that I create
'close all the connections
wkBook.Close SaveChanges:=True
xlApp.Quit
Set wkSheet = Nothing
Set wkBook = Nothing
Set xlApp = Nothing
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
If anyone can tell me if there's anything wrong with the code I have above, and if there's a more efficient way of doing it, it would be greatly appreciated.
I have a feeling that the code I wrote hogs a lot of the resources which causes all the other programs to stall, but I don't know why it hangs in the first place. The database has around 20,000 to be exported to Excel, but it should be able to handle that right?
The code below is what I use to open the connection to Access and Excel. If there's a more efficient way of opening the connections please let me know. I also create recordsets using sql statements to filter the data i want to transfer.
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim xlApp As Excel.Application
Dim wkBook As Excel.Workbook
Dim wkSheet As Excel.Worksheet
cnn.Mode = adModeRead 'open the db in readonly mode
cnn.Mode = adModeShareExclusive 'open the db exclusively
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dbName + ";"
Set xlApp = New Excel.Application 'create new hidden instance of Excel
Set rst = cnn.Execute("SELECT FIELD1, FIELD2 FROM TABLE1"
'example of a recordset that I create'close all the connections
wkBook.Close SaveChanges:=True
xlApp.Quit
Set wkSheet = Nothing
Set wkBook = Nothing
Set xlApp = Nothing
rst.Close
cnn.Close
Set rst = Nothing
Set cnn = Nothing
If anyone can tell me if there's anything wrong with the code I have above, and if there's a more efficient way of doing it, it would be greatly appreciated.
