Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Connection from Excel
Hello,
I have written the following code into an Excel Sheet command button which attempts to access a query that is housed in a Access DB and export the data into a excel sheet. Private Sub CommandButton1_Click() 'Create a Recordset from all the records in the DuplicateCandidate Query Dim sEcruiterMaster As String Dim conn As New ADODB.Connection Dim rs As ADODB.Recordset 'Inform user what command will do Beep MsgBox "This will run the Duplicate Candidate Report for Campus.", vbOKCancel, "Information" 'Finds and run the query from the database EcruiterMaster = "\\Pathe to Ecruiter Campus DB" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & EcruiterMaster & ";" conn.CursorLocation = adUseClient Set rs = conn.Execute("BM_DuplicateCandidate", , adCmdQuery) 'Create a new workbook in Excel Dim xlApp As Object Dim xlBook As Object Dim xlSheet As Object Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) xlApp.Visible = True 'Transfer the data to Excel xlSheet.Range("A2").CopyFromRecordset rs End Sub However, when I run the code, I get the error "Error in the DLL". I used this same command button a year ago and it worked without any problems. Since then i have not used it and when I came back to it this year i keep getting the same error. I have checked all my references to ensure that have been inserted and am unsure what else to do. Any help anyone can provide is much appreciated. Many thanks Very Lost! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Database Connection from Excel
You don't say exactly when the error occurs, which doesn't make it easy to
say where the problem is arising. If the problem is occuring when you are trying to create the connection or return the recordset (ie. with the ADO system) then you could check which version of ADO you are referencing in your project? The latest version is ADO 2.8. Any machine configuration changes in the last two years might have changed some of the behaviours that used to work. To get the newest version requires installing MDAC 2.8, which updates components across the data access spectrum. It also appears to leave references to the older ADO versions intact although I think they use the same underlying dll. The file is here, but watch out for word wrapping. http://www.microsoft.com/downloads/d...DisplayLang=en Robin Hammond www.enhanceddatasystems.com "Lost!" wrote in message ... Hello, I have written the following code into an Excel Sheet command button which attempts to access a query that is housed in a Access DB and export the data into a excel sheet. Private Sub CommandButton1_Click() 'Create a Recordset from all the records in the DuplicateCandidate Query Dim sEcruiterMaster As String Dim conn As New ADODB.Connection Dim rs As ADODB.Recordset 'Inform user what command will do Beep MsgBox "This will run the Duplicate Candidate Report for Campus.", vbOKCancel, "Information" 'Finds and run the query from the database EcruiterMaster = "\\Pathe to Ecruiter Campus DB" conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & EcruiterMaster & ";" conn.CursorLocation = adUseClient Set rs = conn.Execute("BM_DuplicateCandidate", , adCmdQuery) 'Create a new workbook in Excel Dim xlApp As Object Dim xlBook As Object Dim xlSheet As Object Set xlApp = CreateObject("Excel.Application") Set xlBook = xlApp.Workbooks.Add Set xlSheet = xlBook.Worksheets(1) xlApp.Visible = True 'Transfer the data to Excel xlSheet.Range("A2").CopyFromRecordset rs End Sub However, when I run the code, I get the error "Error in the DLL". I used this same command button a year ago and it worked without any problems. Since then i have not used it and when I came back to it this year i keep getting the same error. I have checked all my references to ensure that have been inserted and am unsure what else to do. Any help anyone can provide is much appreciated. Many thanks Very Lost! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel connection to ODBC database | Excel Discussion (Misc queries) | |||
Serial Letter- Connection with Excel database | Excel Discussion (Misc queries) | |||
MS Access Database Connection problem in Excel XP 2002 | Excel Programming | |||
DataBase Connection | Excel Programming | |||
help with database connection - urgent | Excel Programming |