Hello Joel and CLR..
I am facing the similar problem (the one shown below by CLR).. I am able to run the below macro on my PC; however my colleagues are unable to run the same on their PC's and are getting the ODBC general 1004 error.
Also, i tried running the code post removing DefaultDir and Driver Id as well. Howeverm unable to achieve the sucess.
Kindly help.
COde::
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 10/07/2013 by wadhwra
'
'
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=I:\Sharepoint MIS\Misc\New Microsoft Office Access Application (2).mdb;DefaultDir=I:\Sharepoint MIS\" _
), Array("Misc;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;")), _
Destination:=Range("A1"))
.CommandText = Array( _
"SELECT `Daily New Trades`.`Centre Code`, `Daily New Trades`.`Product Code`, `Daily New Trades`.`Short Name`, `Daily New Trades`.`Account No`, `Daily New Trades`.`Deal No`, `Daily New Trades`.`Contract" _
, _
" Type Code`, `Daily New Trades`.`Deal Date`, `Daily New Trades`.`Maturity Date`, `Daily New Trades`.`Branch CIS`, `Daily New Trades`.`Legal CIS`, `Daily New Trades`.`MTM GBP`, `Daily New Trades`.`Actu" _
, _
"al MTM GBP`, `Daily New Trades`.`System Code`, `Daily New Trades`.`GRS Trade Key ID`, `Daily New Trades`.`Trade Key ID`, `Daily New Trades`.`Structure Flag`, `Daily New Trades`.`Deal Ticket No / GFX R" _
, _
"ef`, `Daily New Trades`.`PFE GBP`, `Daily New Trades`.`CLU GBP`" & Chr(13) & "" & Chr(10) & "FROM `Daily New Trades` `Daily New Trades`" _
)
.Name = "Query from MS Access Database"
.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
below is the code for your reference..
Recorded macro aren't very robust. I always edit recorded macros eliminating
the unecessary items and replace SELECTION with absolute cell references.
"CLR" wrote:
> Hi Joel.........
> Well, you saved the day, or week, or several weeks that I've been working on
> that problem........just as you suggested, I removed everything from the
> macro that I could and have it still function on my good machine and then
> took it to my User and it ran perfectly on his machine......SUCCESS!!!
>
> Many, many thanks, my friend,
> Vaya con Dios,
> Chuck, CABGx3
>
>
> "Joel" <[email protected]> wrote in message
> news:[email protected]...
> > You will never get an error until you get to the Refresh line. I would
> > remove some unecessary line in the command until you get it to work.
> > First
> > try on the working computer to eliminate as many unecessary items as
> > possible
> > and check to make sure it still works. then put these changes in the
> > non-working PC.
> >
> > Here are things that I think are un-needed
> >
> > 1) DefaultDir=
> > 2) DriverId=281
> >
> > Also make sure all your variables are references the correct path names
> > like
> > newfile2.
> >
> > "CLR" wrote:
> >
> >> Hi Joel........
> >> Thanks for the comeback.....
> >> Here's the code...
> >> With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
> >> "ODBC;DSN=MS Access Database;DBQ=" & NewFile & ";DefaultDir= " _
> >> ), Array( _
> >> ";DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;" _
> >> )), Destination:=Range("A1"))
> >> .CommandText = Array( _
> >> "SELECT `NPR Database`.`Disposition Date`, `NPR
> >> Database`.`Inspection Date`, `NPR Database`.`NPR Origin`, `NPR
> >> Database`.`NPR
> >> Number`, `NPR Database`.`Part Number`, `NPR Database`.`Serial Number`,
> >> `NPR" _
> >> , _
> >> " Database`.`Vendor Code`, `NPR Database`.`Vendor Name`, `NPR
> >> Database`.`No of Defects`, `NPR Database`.`Qty RTV`, `NPR
> >> Database`.`Defect
> >> Description`, `NPR Database`.`Corrective Action`" & Chr(13) & "" &
> >> Chr(10) &
> >> "FROM `" _
> >> , _
> >> "" & newfile2 & "`.`NPR Database` `NPR Database`" & Chr(13) & ""
> >> &
> >> Chr(10) & "ORDER BY `NPR Database`.`Vendor Code`" _
> >> )
> >> .Name = "Query from MS Access Database"
> >> .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
> >>
> >> When failing, the bottom line, ".Refresh BackgroundQuery:=False" is the
> >> one
> >> that's highlighted.....but then that's the case with almost any error
> >> within
> >> this routine.
> >>
> >> My "Error Trapping" setting is "Break on unhandled errors".....if my
> >> User's
> >> computer has a different setting could that be the reason? (He's tied up
> >> right now so I can't check his setting).
> >>
> >> Like I said, the code works fine on my computer, just not on his.
> >>
> >> Vaya con Dios,
> >> Chuck, CABGx3
> >>
> >>
> >>
> >> "Joel" wrote:
> >>
> >> > It would help to know which line of code is failing. One thing to
> >> > check is
> >> > the error option in BA. go to VBA menu Tools - Options - General -
> >> > Error
> >> > Trapping.
> >> >
> >> > "CLR" wrote:
> >> >
> >> > > Hi All.........
> >> > > I have a little macro that queries an ACCESS file and returns desired
> >> > > values. The program works fine on my computer, but when my user
> >> > > tries to run
> >> > > it on his computer, (same versoion of Excel, 2000), he gets the
> >> > > following
> >> > > error.
> >> > >
> >> > > Run Time Error '1004' General ODBC Error
> >> > >
> >> > > Obviously some difference between the two machines, but who knows
> >> > > what to do
> >> > > to fix it?
> >> > >
> >> > > TIA
> >> > > Vaya con Dios,
> >> > > Chuck, CABGx3
> >> > >
>
>
>
