Running Create Table Query in VBA (1 Viewer)

Hanz

Registered User.
Local time
Today, 08:46
Joined
Aug 9, 2018
Messages
25
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

AWF VIP
Local time
Today, 07:46
Joined
Mar 9, 2014
Messages
5,423
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
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:46
Joined
May 7, 2009
Messages
19,169
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

Registered User.
Local time
Today, 08:46
Joined
Aug 9, 2018
Messages
25
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

Registered User.
Local time
Today, 08:46
Joined
Aug 9, 2018
Messages
25
Hi Arnelgp, thanks for your reply. for just two spaces it puzzled me for hours. :)
 

June7

AWF VIP
Local time
Today, 07:46
Joined
Mar 9, 2014
Messages
5,423
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.
 
Last edited:

Hanz

Registered User.
Local time
Today, 08:46
Joined
Aug 9, 2018
Messages
25
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")
 

Users who are viewing this thread

Top Bottom