Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Run time error '1004' using TextBox1.Text as SQL query criteria
I get "Run time error '1004' SQL Syntax Error" when running the following VBA:
Sub CommandButton1_Click() With ActiveSheet.QueryTables.Add(Connection:= _ "ODBC;DSN=Springbrook2;UID=ODBCUSER;HOST=millbrae3 ;PORT=26102;DB=ssi.db;", _ Destination:=Range("A1")) .CommandText = Array( _ "SELECT GL_History_0.Ref_Batch_No, GL_History_0.Ref_Batch_Month, GL_History_0.Ref_Batch_Year, GL_Journal_Entry_0.JE_Date, GL_Journal_Entry_0.System, GL_History_0.Acct_1, GL_History_0.Acct_2, GL_History" _ , _ "_0.Acct_3, GL_History_0.Acct_4, GL_Journal_Entry_0.Description, GL_History_0.CR_Amount, GL_History_0.DR_Amount" & Chr(13) & "" & Chr(10) & "FROM PUB.GL_History GL_History_0, PUB.GL_Journal_Entry GL_Journal_Entry_0" & Chr(13) & "" & Chr(10) & "WHERE GL_Hist" _ , _ "ory_0.Journal_Entry = GL_Journal_Entry_0.Journal_Entry AND ((GL_Journal_Entry_0.System='UB') AND (GL_Journal_Entry_0.JE_Date={d TextBox1.Text}))" _ ) .Name = "Springbrook UB Interface 3-27-08" .FieldNames = True .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .SavePassword = False .SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .Refresh BackgroundQuery:=False End With End Sub The debugger highlights the ".Refresh BackgroundQuery:=False". Upon clicking the "CommandButton1", I'm trying to have the macro connect to a pre-defined database query ("Springbrook UB Interface 3-27-08"). What I am also toying with is having the user enter the effective date in the UserForm1 TextBox1.Text field as criteria for the query. I think that may be where I might be having a problem. Any suggestions are appreciated. Thank you! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
Run Time 1004 Error: Application or Object Difine Error | Excel Programming | |||
Web Query Error 1004 - Cannot insert columns... | Excel Programming | |||
run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
Help with error 1004 in database query | Excel Programming |