Count from DBF to Excel

[Swi]

I am trying the following code and keep getting an error:

Dim conn As ADODB.Connection
Dim numofrecscopied As Long
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\;Extended Properties=dBASE 5.0;User ID=;Password="
conn.Open
conn.Execute "SELECT STATE, COUNT(*) AS QUANTITY INTO [Excel 8.0;" & _
"Database=C:\TEST.XLS].[Sheet1] FROM " & _
"TEST.DBF", numofrecscopied
conn.Close
Set conn = Nothing
MsgBox numofrecscopied & " records copied to Excel!", vbInformation

The error is Query Input Must Contain At Least One Table Or Query. Is there a way I can modify this to work? Thanks.

Swi

 

[gmmastros]

I don't know if this will correct your problem, but your query is flawed. Let me explain.

When you use an aggregate within a query AND you include non-aggregated columns, you MUST use a GROUP BY clause.

So, what's an aggregate? This is most easily explained by giving examples. Examples are Avg, Sum, Count, StdDev, etc...

It appears as though you want to return a list of states and the number of records for each state.

If you don't include a non-aggregate column, you don't need to use Group By. For example, if you want to know how many records exist in the table...

[tt][blue]Select Count(*) As RowCount From TableName[/blue][/tt]

If you want to know how many rows for each state, then include a group by.

[tt][blue]Select State, Count(*) As Rowcount From TableName Group By State[/blue][/tt]

Based on your error message, I don't think this will solve your problem. However, it's likely that this would be your 'next' problem.

Working with excel files can be a bit finicky. I would suggest that you first create the excel file and then try your code. You may be getting this error because the excel file doesn't exist. (I could be wrong about this, though).



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Swi]

Yes, you are correct. I cut and pasted the first part of the SQL statement from a current app and missed the GROUP BY statement. Thanks for pointing that error out. I have corrected it and created an Excel file with the output name just to thoroughly test but it still does not work. Thanks.

Swi

 

[SmokinWrek]

Swi,

Sounds like you might need to use the IN clause of the SELECT INTO statement to accomplish this. The SELECT INTO is expecting a table name into which to place the returned records. The IN clause will allow you to specify another database (in your case, an Excel file).

Example:

SELECT Persons.* INTO Persons IN 'Backup.mdb'
FROM Persons

In your case, the 'Backup.mdb' would be replaced with your Excel file. However, I'm not sure what you'd use in place of the "Persons" table name after INTO... maybe the sheet name of your Excel file?

I don't know if you'll find it helpful, but I found out about the IN clause here:

http://www.w3schools.com/sql/sql_select_into.asp

Kevin

 

[Swi]

I'll look into it when I get a chance. Keep in mind that I want to pull the data from a DBF and place it is Excel. Right now, I create a recordset from the DBF and through Excel automation place the return results into Excel.

Swi

 

[Swi]

When using Access it comes out fine. It has to be something with the DBF.

Dim conn As ADODB.Connection
Dim numofrecscopied As Long
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionString= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False"
conn.Open
conn.Execute "SELECT STATE, COUNT(*) AS QUANTITY INTO [Excel 8.0;" & _
"Database=C:\TEST.XLS].[Sheet1] FROM " & _
"TEST", numofrecscopied
conn.Close
Set conn = Nothing
MsgBox numofrecscopied & " records copied to Excel!", vbInformation

Swi

 

[tsuji]

Group by" is always missing.

 

[Swi]

I know. I just reposted the code above and changed the connection string.

Swi

 

[tsuji]

But that is runtime error.

 

[Swi]

I understand that it is an error and that I do need a group by statement. That however is not the solution to my original issue. I will just keep the code as I have it now which is returning a recordset from the DBF file and automating Excel to drop the data into it. Thanks.

Swi

 

[SmokinWrek]

Swi,

I was looking into this a bit more, and if you return a recordset rather than trying to do a SELECT INTO, you might be able to use the CopyFromRecordset method to import the data.

Kevin

 

[BobRodes]

Yes, that should work, but it would be nice to do it in this more concise manner if possible. I agree with SWI that there's something going on with the DBF provider.

 

[Swi]

Yes, right now I am doing a copyfromrecordset:

            '==================================================================
            ' Produces counts by state
            '==================================================================
            SheetCounter = SheetCounter + 1
            Set oXLSheet = oXLBook.Worksheets("Sheet" & SheetCounter)
            oXLSheet.Name = "STATE"
            oXLSheet.Activate
            PlaceInExcel "A1", "A1", "STATE", True
            PlaceInExcel "B1", "B1", "COUNT", True
            lblStatus.Caption = "Generating State counts . . ."
            DoEvents
            ProgressBar.Value = ProgressBar.Value + 1
            .CursorLocation = adUseServer
            .Open Trim$("SELECT [STATE], COUNT(*) AS [QUANTITY]" & _
            "FROM [" & FileName & "] " & WhereByClause) & " GROUP BY [STATE]", conn, adOpenStatic, adLockReadOnly, adCmdText
            oXLSheet.Range("A2" & ":B" & rs.RecordCount + 1).CopyFromRecordset rs
            PlaceInExcel "A" & rs.RecordCount + 3, "A" & rs.RecordCount + 3, "Total Records", True
            PlaceInExcel "B" & rs.RecordCount + 3, "B" & rs.RecordCount + 3, _
            "=SUM(B2:B" & rs.RecordCount + 1 & ")", True
            oXLApp.Columns("A:B").EntireColumn.AutoFit
            .Close

Swi