Database Connection from Excel

[Lost!]

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!

 

[Robin Hammond]

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/...e3-c795-4b7d-b037-185d0506396c&DisplayLang=en

Robin Hammond
www.enhanceddatasystems.com