Empty Recordset

  • Thread starter T5925MS via AccessMonster.com
  • Start date
T

T5925MS via AccessMonster.com

I'm looking for help with copying a recordset to Excel. I created and tested
a query then copied and formatted the sql to code. When the spreadsheet opens,
there are no pasted records. I receive a code error, "command text was not
set for the command object". Here's the code I'm using:

Private Sub cmdExport_Click()

'Create a recordset named MyRecordset.
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn

'Build the SQL statement
Dim MySQL As String
MySQL = "SELECT Representatives.CID, "
MySQL = MySQL & " Representatives.NIC, "
MySQL = MySQL & " Representatives.[Last Name], "
MySQL = MySQL & " Representatives.[First Name], "
MySQL = MySQL & " [First Name] & ' ' & [Last Name] AS "
MySQL = MySQL & " [Full Name], Positions.Position, "
MySQL = MySQL & " Representatives.[Home Location Code], "
MySQL = MySQL & " [Location Assignments]."
MySQL = MySQL & " [Location Code] AS "
MySQL = MySQL & " [Assigned Location Code], "
MySQL = MySQL & " Locations.[Location Name], "
MySQL = MySQL & " Locations.[Local Union Number], "
MySQL = MySQL & " Representatives.Email, "
MySQL = MySQL & " Representatives.[Mobile Line], "
MySQL = MySQL & " [Location Phone Numbers]."
MySQL = MySQL & " [Outside Line], "
MySQL = MySQL & " [Location Phone Numbers].[Tie Line]"
MySQL = MySQL & " FROM Status INNER JOIN ((Positions "
MySQL = MySQL & " INNER JOIN Representatives ON "
MySQL = MySQL & " Positions.[Position Code] = "
MySQL = MySQL & " Representatives.[Position Code]) "
MySQL = MySQL & " INNER JOIN ((Locations INNER JOIN "
MySQL = MySQL & " [Location Assignments] ON "
MySQL = MySQL & " Locations.[Location Code] = "
MySQL = MySQL & " [Location Assignments]."
MySQL = MySQL & " [Location Code]) INNER JOIN "
MySQL = MySQL & " [Location Phone Numbers] ON "
MySQL = MySQL & " Locations.[Location Code] = "
MySQL = MySQL & " [Location Phone Numbers]."
MySQL = MySQL & " [Location Code]) ON "
MySQL = MySQL & " ([Location Assignments].CID = "
MySQL = MySQL & " Representatives.CID) AND "
MySQL = MySQL & " (Positions.[Position Code] = "
MySQL = MySQL & " [Location Phone Numbers]."
MySQL = MySQL & " [Position Code])) ON Status."
MySQL = MySQL & " [Status Code] = "
MySQL = MySQL & " Representatives.[Status Code]"
MySQL = MySQL & " WHERE (((Representatives."
MySQL = MySQL & " [Status Code])='1') AND "
MySQL = MySQL & " ((Locations.[Active Location])=Yes))"
MySQL = MySQL & " ORDER BY Representatives.[Last Name], "
MySQL = MySQL & " Representatives.[First Name];"

'Export the recordset to Excel
Dim MySheetPath As String
MySheetPath = "C:\Mike\AccessExport\ListofRepresentatives.xls"

'Set up object variables to refer to Excel and objects.
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook.
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen.
Xl.Visible = True
XlBook.Windows(1).Visible = True

'Define th etopmost sheet in the Workbook as XLSheet.
Set XlSheet = XlBook.Worksheets(1)

'Copy the recordset to worksheet starting at cell A2
XlSheet.Range("A2").CopyFromRecordset MyRecordset

'Cleanup and end with worksheet visible on the screen.
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

End Sub
 
T

T5925MS via AccessMonster.com

I found the problem...First, I forgot to include MyRecordset.Open MySQL, and
second, I have a field named Position, which is a reserved word and therefore
needs to be enclosed in brackets, e.g [Position].
I'm looking for help with copying a recordset to Excel. I created and tested
a query then copied and formatted the sql to code. When the spreadsheet opens,
there are no pasted records. I receive a code error, "command text was not
set for the command object". Here's the code I'm using:

Private Sub cmdExport_Click()

'Create a recordset named MyRecordset.
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim MyRecordset As New ADODB.Recordset
MyRecordset.ActiveConnection = cnn

'Build the SQL statement
Dim MySQL As String
MySQL = "SELECT Representatives.CID, "
MySQL = MySQL & " Representatives.NIC, "
MySQL = MySQL & " Representatives.[Last Name], "
MySQL = MySQL & " Representatives.[First Name], "
MySQL = MySQL & " [First Name] & ' ' & [Last Name] AS "
MySQL = MySQL & " [Full Name], Positions.Position, "
MySQL = MySQL & " Representatives.[Home Location Code], "
MySQL = MySQL & " [Location Assignments]."
MySQL = MySQL & " [Location Code] AS "
MySQL = MySQL & " [Assigned Location Code], "
MySQL = MySQL & " Locations.[Location Name], "
MySQL = MySQL & " Locations.[Local Union Number], "
MySQL = MySQL & " Representatives.Email, "
MySQL = MySQL & " Representatives.[Mobile Line], "
MySQL = MySQL & " [Location Phone Numbers]."
MySQL = MySQL & " [Outside Line], "
MySQL = MySQL & " [Location Phone Numbers].[Tie Line]"
MySQL = MySQL & " FROM Status INNER JOIN ((Positions "
MySQL = MySQL & " INNER JOIN Representatives ON "
MySQL = MySQL & " Positions.[Position Code] = "
MySQL = MySQL & " Representatives.[Position Code]) "
MySQL = MySQL & " INNER JOIN ((Locations INNER JOIN "
MySQL = MySQL & " [Location Assignments] ON "
MySQL = MySQL & " Locations.[Location Code] = "
MySQL = MySQL & " [Location Assignments]."
MySQL = MySQL & " [Location Code]) INNER JOIN "
MySQL = MySQL & " [Location Phone Numbers] ON "
MySQL = MySQL & " Locations.[Location Code] = "
MySQL = MySQL & " [Location Phone Numbers]."
MySQL = MySQL & " [Location Code]) ON "
MySQL = MySQL & " ([Location Assignments].CID = "
MySQL = MySQL & " Representatives.CID) AND "
MySQL = MySQL & " (Positions.[Position Code] = "
MySQL = MySQL & " [Location Phone Numbers]."
MySQL = MySQL & " [Position Code])) ON Status."
MySQL = MySQL & " [Status Code] = "
MySQL = MySQL & " Representatives.[Status Code]"
MySQL = MySQL & " WHERE (((Representatives."
MySQL = MySQL & " [Status Code])='1') AND "
MySQL = MySQL & " ((Locations.[Active Location])=Yes))"
MySQL = MySQL & " ORDER BY Representatives.[Last Name], "
MySQL = MySQL & " Representatives.[First Name];"

'Export the recordset to Excel
Dim MySheetPath As String
MySheetPath = "C:\Mike\AccessExport\ListofRepresentatives.xls"

'Set up object variables to refer to Excel and objects.
Dim Xl As Excel.Application
Dim XlBook As Excel.Workbook
Dim XlSheet As Excel.Worksheet

'Open an instance of Excel, open the workbook.
Set Xl = CreateObject("Excel.Application")
Set XlBook = GetObject(MySheetPath)

'Make sure everything is visible on the screen.
Xl.Visible = True
XlBook.Windows(1).Visible = True

'Define th etopmost sheet in the Workbook as XLSheet.
Set XlSheet = XlBook.Worksheets(1)

'Copy the recordset to worksheet starting at cell A2
XlSheet.Range("A2").CopyFromRecordset MyRecordset

'Cleanup and end with worksheet visible on the screen.
MyRecordset.Close
Set cnn = Nothing
Set Xl = Nothing
Set XlBook = Nothing
Set XlSheet = Nothing

End Sub
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access to Excel Automation Runtime Error 3
ADO -DAO problem? 18
Create Recordset in VBA 1
update query not working 1
Filter Form Based on Subform Combo Field Problem 1
Assistance with vbYesNo 3
ADODB.Connection run-time error -2147467259 (80004005) 3
recordset query 2

Top