pwigington
11-10-2004, 08:18 AM
Hello,
I am currently creating an Access database to warehouse security information for our associates. I have created a form which allows users to create custom reports based on fields from the EmployeeInfo table. However, some users requested that implement an option to export the report info to Excel. Now, I can easily export the Access report info to Excel, but the Access Report does not pass the label captions to Excel and I end up with column names like "Text1" and "Text2" rather than "FirstName" and "LastName" as shown on the report. Rather than export the report, I thought it would be easier to create a new table and then export that table to Excel. Below is my code:
Create Table:
'Prepare New Table
Dim MyConn As ADODB.Connection
Dim strQuery
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AMS\EmployeeInfo.mdb;" & _
"jet oledb:system database=" & _
"c:\AMS\Secured.mdw;User ID=xphillip;Password=aaron215"
MyConn.Open
MyConn.Execute strCreate
MyConn.Close
MyConn.Open
strQuery = "Insert Into NewTab SELECT " & strData & " FROM EmployeeInfo"
MyConn.Execute strQuery
MyConn.Close
Export Table to Excel:
DoCmd.OutputTo acOutputTable, "NewTab", "*.xls", "C:\Report.xls"
ShellExecute Me.hwnd, vbNullString, "C:\Report.xls", vbNullString, "C:\", SW_ShowMaximized
When I execute this code with a single button click, I receive a Run-Time '3011' Error that states that the Jet DB Engine cannot find the object "NewTab". If I separate this code into two buttons (one to create the table and then one to export to Excel and open the Excel file), it works just fine. Does anyone have any idea what is causing this error? It seems that Access is not registering the creation of the new table until after the Sub has run.
I am currently creating an Access database to warehouse security information for our associates. I have created a form which allows users to create custom reports based on fields from the EmployeeInfo table. However, some users requested that implement an option to export the report info to Excel. Now, I can easily export the Access report info to Excel, but the Access Report does not pass the label captions to Excel and I end up with column names like "Text1" and "Text2" rather than "FirstName" and "LastName" as shown on the report. Rather than export the report, I thought it would be easier to create a new table and then export that table to Excel. Below is my code:
Create Table:
'Prepare New Table
Dim MyConn As ADODB.Connection
Dim strQuery
Set MyConn = New ADODB.Connection
MyConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\AMS\EmployeeInfo.mdb;" & _
"jet oledb:system database=" & _
"c:\AMS\Secured.mdw;User ID=xphillip;Password=aaron215"
MyConn.Open
MyConn.Execute strCreate
MyConn.Close
MyConn.Open
strQuery = "Insert Into NewTab SELECT " & strData & " FROM EmployeeInfo"
MyConn.Execute strQuery
MyConn.Close
Export Table to Excel:
DoCmd.OutputTo acOutputTable, "NewTab", "*.xls", "C:\Report.xls"
ShellExecute Me.hwnd, vbNullString, "C:\Report.xls", vbNullString, "C:\", SW_ShowMaximized
When I execute this code with a single button click, I receive a Run-Time '3011' Error that states that the Jet DB Engine cannot find the object "NewTab". If I separate this code into two buttons (one to create the table and then one to export to Excel and open the Excel file), it works just fine. Does anyone have any idea what is causing this error? It seems that Access is not registering the creation of the new table until after the Sub has run.