Running Create Table Query in VBA

[Hanz]

Hi,


I tried to use the same process of updating my table source using SQL. Which is i only copy the SQL from design view and manipulate it in VBA. This is my first time to use this process in a query that will create table. I just don't know why my code returns "query input must contain at least one table or query". When i run this SQL in query design view, it works fine but when i incorporate this in my VBA code it returns 3067 error.



Please see my code below.

Private Sub btnExcel_Click()
Dim SQL As Variant

SQL = "SELECT tbl_Tariff.Description, tbl_Tariff.TariffCode, tbl_Tariff.JobID INTO Tariff_Table" _
& "FROM tbl_Tariff" _
& "WHERE (((tbl_Tariff.JobID)=[Forms]![mainTariffSearch]![txtClearID]) AND ((tbl_Tariff.Choose)=True));"

DoCmd.RunSQL SQL

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "tariff_Table", "C:\Exports\Spreadsheet.xls"

End Sub

 

[June7]

Need spaces in concatenated strings so words don't run together in compiled statement. Add space in front of FROM and WHERE or at end of previous line.

Advise not to implement code that routinely modifies database. Build a permanent table then INSERT and DELETE records. This is known as using a temp Table.

Explore CopyFromRecordset method http://www.accessmvp.com/kdsnell/EXCEL_Export.htm#ExpCopyFromRst

 

[arnelgp]

SQL = "SELECT tbl_Tariff.Description, tbl_Tariff.TariffCode, tbl_Tariff.JobID INTO Tariff_Table" _
& "FROM tbl_Tariff" _
& "WHERE (((tbl_Tariff.JobID)=[Forms]![mainTariffSearch]![txtClearID]) AND ((tbl_Tariff.Choose)=True));"

you need to Add Spaces:

SQL = "SELECT tbl_Tariff.Description, tbl_Tariff.TariffCode, tbl_Tariff.JobID INTO Tariff_Table " _
& "FROM tbl_Tariff " _
& "WHERE (((tbl_Tariff.JobID)=[Forms]![mainTariffSearch]![txtClearID]) AND ((tbl_Tariff.Choose)=True));"

 

[Hanz]

Hi June7, the link is very useful. I studied the link and i came up to the code below. I wanted to add the current time in the file name and tried to add "& now() " in my code but i can't get this one work.

Private Sub btnExcel_Click()

Dim db As Database
Dim qdfTariff As QueryDef
Dim strSQL As String, strQDF As String
Dim JobNumber As Double

JobNumber = InputBox("Which Job Spreadsheet you want to send to excel?", "JobID Input")
Me.txtClearID = JobNumber


Set db = CurrentDb

strSQL = "SELECT tbl_Tariff.Description, tbl_Tariff.TariffCode, tbl_Tariff.JobID" _
& " FROM tbl_Tariff" _
& " WHERE (((tbl_Tariff.JobID)=[Forms]![mainTariffSearch]![txtClearID]) AND ((tbl_Tariff.Choose)=True));"

strQDF = "_TempSpreadsheet_"
Set qdfTariff = db.CreateQueryDef(strQDF, strSQL)
qdfTariff.Close
Set qdfTariff = Nothing

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQDF, "c:\Exports\Spreadsheet_" & JobNumber

db.QueryDefs.Delete strQDF
db.Close
Set db = Nothing

End Sub

 

[Hanz]

Hi Arnelgp, thanks for your reply. for just two spaces it puzzled me for hours.

 

[June7]

Windows has issue with / and : that are default punctuation in date/time. Use Format() to substitute characters. Do you really need time part?

Format(Now(), "yyyy_mm_dd hh.nn.ss")

Format(Date(), "yyyy_mm_dd")

Or don't even use the underscores and periods.

 

[Hanz]

Hi June7, yes i would prefer to have date and time so to avoid same file name when the user exports to excel. i got this code working fine for me. Thank you.

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQDF, "c:\Exports\Spreadsheet_" & JobNumber & "_" & Format(Now(), "ddmmyyyy_hhnnss")