General ODBC Error - Run time error 1004

John Wood

Board Regular
Joined
Sep 15, 2008
Messages
118
Please help me before this laptop becomes a Frisbee!

I almost have this working but I'm getting stuck on this error every time I execute it. I have an ODBC with read-only privileges and I recorded part of the code from the macro. The "from" and "to" date fields I added ( I think I'm correct). I would truly appreciate the help.

Here's the code:

Private Sub CommandButton1_Click()
Dim smpldatea As Date
Dim smpldateb As Date

Dim fdate1 As String
Dim fdate2 As String

smpldatea = UserForm1.TextBox1.Text
smpldateb = UserForm1.TextBox2.Text

fdate1 = Format(smpldatea, "m/d/yyyy hh:mm:ss")
fdate2 = Format(smpldateb, "m/d/yyyy hh:mm:ss")

Sheets("Sheet1").Activate

Range("A1:H10000").ClearContents


With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DRIVER={Oracle in OraHome92};SERVER=XXXXXX;UID=DT_I;PWD=RO2ACCESS;DBQ=XXXXXXP;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10" _
), Array( _
";FDL=10;LOB=T;RST=T;GDE=F;FRL=Lo;BAM=IfAllSuccessful;MTS=F;MDI=Me;CSR=F;FWC=F;PFC=10;TLO=O;" _
)), Destination:=Range("A1"))
.CommandText = Array( _
"SELECT DT_CENTERLINE_AUDIT.AUDIT_DATE, DT_CENTERLINE_AUDIT.CENTERLINE_ID, DT_CENTERLINE_AUDIT.COMPLIANT, DT_CENTERLINE_AUDIT.CORRECTED, DT_CENTERLINE_AUDIT.LINE, DT_CENTERLINE_AUDIT.LOCATION" & Chr(13) & "" & Chr(10) & "FROM DTD" _
, _
"BA.DT_CENTERLINE_AUDIT DT_CENTERLINE_AUDIT" & Chr(13) & "" & Chr(10) & "WHERE (DT_CENTERLINE_AUDIT.AUDIT_DATE>= '" & fdate1 & "' And DT_CENTERLINE_AUDIT.AUDIT_DATE<= '" & fdate2 & "') AND (DT_CENTERLINE_AUDIT.L" _
, "OCATION='0902')")
.Name = "Query from YODA2_2"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
That's a tall order for you to debug but 100 times more difficult for someone else to attempt. With ODBC, the best thing is to start as simply (a simple read) as you can, and add pieces until it breaks. Then you have some idea of what broke it but this...
 
Upvote 0
I'm thinking it has more to do with the worksheet I'm trying to post the results to. About an hour ago, before I got ticked and tried more solutions I was able to see that the query itself was present in the cells on Sheet1. I reopened the query from the cell and I did get results back after closing the MSQuery box.
 
Upvote 0
I'm not familiar with using Arrays to set connection and command properties, I always used strings.

Best way to proceed is to make a query using the built in MS query tools (import, external data, etc).

Once the query is built and downloaded, hop into VBE, go to immediate window and type:

print mysheet.querytables(0).connection

and same for Command

and whatever else you need.

Then use those values in your macro.
 
Upvote 0
Sorry Chris, you're talking to someone very new to this. Strings? How is that different from what I'm doing? thanks for the reply, btw, I'm going to do that right now.
 
Upvote 0
Sorry, I was just paraphrasing there. Type:

Print Worksheets("yoursheetname").Querytables(0).Connection

This assumes that the query is the first one on that particular sheet, you may need to go up to (1) or (2) if you have multiple queries.

The bit about strings vs. arrays: A string is just text encased in quotes, "hello there". All I'm saying is you can specify connection settings using strings:

With ActiveSheet.QueryTables.Add(Connection:= "ODBC;DRIVER={Oracle in OraHome92};SERVER=XXXXXX;UID=DT_I;PWD=RO2ACCESS;DBQ=XXXXXXP;DBA=W;APA=T;EXC=F;XSM=Default;FEN=T;QTO=T;FRC=10" etc etc
 
Upvote 0
Not sure I'm following everything here but John, if you're in the code window and use print you'll get the "object required" error. Do it in the immediate window (CTL-G)
 
Upvote 0
I was able to get the values and they're right with what I've been using. I've started from the beginning again. The problem is the way I'm putting the date variable into the array. The original code uses {ts ' 01-01-01'... } and I'm trying to use a textbox value instead.
 
Upvote 0
I am having a similar issue can someone help?

Hello.


This code previously worked and suddenly it will not refresh the back ground query anymore:

Sub LoadFromDb(sDb As String, sSql As String, sTab As String, sPath As String, sRange As String)
With Worksheets(sTab).QueryTables.Add( _
Connection:=Array(Array( _
"ODBC;DRIVER={Microsoft Access Driver(*.mdb, *.accdb)};DBQ=" & sPath & sDb & ";DefaultDir=" & sPath & ";" _
), Array( _
"DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
)), Destination:=Worksheets(sTab).Range(sRange))
.CommandText = sSql
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
'DeleteRangeNames
End Sub

It runs fine until I get to the .Refresh BackgroundQuery:=False after it tries to execute the code I get a run time error 1004 General ODBC error. I am passing SQL code to run in the database, and I have tested the SQL code in Access and the query runs fine. I am using Office 2007 and Windows 7 64 bit. I am stumped. Can someone please offer some advice?
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top