Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: General ODBC Error - Run time error 1004

  1. #1
    Board Regular
    Join Date
    Sep 2008
    Location
    Ontario, Canada
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Angry General ODBC Error - Run time error 1004

    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

  2. #2
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: General ODBC Error - Run time error 1004

    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...

  3. #3
    Board Regular
    Join Date
    Sep 2008
    Location
    Ontario, Canada
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: General ODBC Error - Run time error 1004

    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.

  4. #4
    Board Regular
    Join Date
    Jun 2002
    Posts
    2,128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: General ODBC Error - Run time error 1004

    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.

  5. #5
    Board Regular
    Join Date
    Sep 2008
    Location
    Ontario, Canada
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: General ODBC Error - Run time error 1004

    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.

  6. #6
    Board Regular
    Join Date
    Sep 2008
    Location
    Ontario, Canada
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: General ODBC Error - Run time error 1004

    I tried to print the querytable but I got a "Object required" error.

  7. #7
    Board Regular
    Join Date
    Jun 2002
    Posts
    2,128
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: General ODBC Error - Run time error 1004

    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

  8. #8
    Board Regular
    Join Date
    Oct 2009
    Location
    Canada, EH
    Posts
    242
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: General ODBC Error - Run time error 1004

    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)

  9. #9
    Board Regular
    Join Date
    Sep 2008
    Location
    Ontario, Canada
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: General ODBC Error - Run time error 1004

    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.

  10. #10
    New Member
    Join Date
    Feb 2012
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: General ODBC Error - Run time error 1004

    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?

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •