Cannot download from Nwind.accdb into Excel, SQL statement does not run

[Peskarik]

Hello!

I have problems downloading data from built-in Access database "Nwind.accdb" into a spreadsheet. When I run the code I get run-time error 80040e10 and "No value given for one or more required parameters".
The problem is with SQL statement. More precicely, with SELECT clause and field name [Company Name]. If I use * - there is no problem, the whole Customers table gets downloaded. If I specify "Address" as the field name, it works as well. But Excel just does not like [Company Name] definition! Can anyone explain what is wrong? The field name is correct, I checked.

Code:

Public Sub PlainTextQuery()
Dim rsData As ADODB.Recordset
Dim sConnect As String
Dim sSQL As String

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Sergey\Documents\Excel VBA\NorthwindDB\Nwind.accdb"

sSQL = "SELECT [Company Name] FROM Customers"

Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect

If Not rsData.EOF Then
Sheets("DBTest").Range("A2").CopyFromRecordset rsData
rsData.Close

With Sheets("DBTest").Range("A1:B1")
.Value = Array("Company", "Contact Name")
.Font.Bold = True
End With
Sheets("DBTest").UsedRange.EntireColumn.AutoFit
Else
rsData.Close
MsgBox "Error: No Records Returned", vbCritical
End If
Set rsData = Nothing
End Sub

I downloaded Nwind.accdb from web, did not have it on my PC. I have Office 2007 and Vista SP1. :(

Sergo

[Carl A]

This worked for me.
Your error stems from the fact you were trying to retrieve records that were not in the query

Public Sub PlainTextQuery()
Dim rsData As ADODB.Recordset
Dim sConnect As String
Dim sSQL As String

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=C:\Users\Sergey\Documents\Excel VBA\NorthwindDB\Nwind.accdb"

sSQL = "SELECT CompanyName, ContactName FROM Customers"

Set rsData = New ADODB.Recordset
rsData.Open sSQL, sConnect

If Not rsData.EOF Then
Sheets("DBTest").Range("A2").CopyFromRecordset rsData
rsData.Close

With Sheets("DBTest").Range("A1:B1")
.Value = Array("CompanyName", "ContactName")
.Font.Bold = True
End With
Sheets("DBTest").UsedRange.EntireColumn.AutoFit
Else
rsData.Close
MsgBox "Error: No Records Returned", vbCritical
End If
Set rsData = Nothing
End Sub

[Peskarik]

Thanks Carl. :friends:
Actually I understood what the problem was, and it was not what you suggested. The database file was defect. :)