Gary:

Did someone move the database or change a field name? General ODBC Error is
hard to debug because of the obvious lack of information it provides. Try
creating the query manually and comparing the Connection and CommandText
properties to what you have in your code.

I don't know the whole story here, but it sure looks like you could use a
parameter query in place of recreating the QueryTable every time. See
http://www.dicks-clicks.com/excel/Ex...htm#Parameters. Also, I
don't see where you delete the old QueryTable before you create the new
one - assuming there is an old one. Again, I don't have all the facts, so
these comments may be way off the mark.

--
Dick Kusleika
MS MVP - Excel
www.dailydoseofexcel.com

wrote:
Hi

I have been attempting to get the following to work, and thought I had
sussed it, however it suddenly has started generating a "1004 General
ODBC Error".

There have been no changes since the last successful trial, so I am a
bit mystified.

Any Help/Advice would be gratefully received

Sub Import()
Dim strConnParam As String
Dim strSql As String
Dim strDbname As String
Dim strPath As String
Dim strName As String
Dim dtFromDate As Date
Dim dtToDate As Date
Dim rngUbr As Range
Dim ws As Worksheet
Dim strUbr As String

DeleteAllQueries
DeleteNames
Set ws = Worksheets("Sheet1")
Set rngUbr = ws.Range("A1")
strUbr = rngUbr
dtFromDate = Format(rngUbr.Offset(0, 1).Value, "mm/dd/yyyy")
dtToDate = Format(rngUbr.Offset(0, 2).Value, "mm/dd/yyyy")
strPath = "J:\Gary\History\"
strName = "j:\gary\history\History.mdb"
strConnParam = "ODBC;DSN=MS Access Database;" & _
"DBQ=" & strName & ";" & _
"DefaultDir=" & strPath & ";" & _
"Driverid=25;" & _
"FIL=MS Access;" & _
"MaxBufferSize=2048;" & _
"PageTimeout=5;" & _
"Destination:=Range(""A4""))"
strSql = "SELECT Data.Ubr," & _
"Data.Date," & _
"Data.BW," & _
"Data.`10 SACM`," & _
"Data.`512 SACM`," & _
"Data.`3 SACM`," & _
"Data.`2 SACM`," & _
"Data.`1 SACM`," & _
"Data.`150 SACM`," & _
"Data.`1500 SACM`," & _
"Data.`750 SACM`," & _
"Data.`300 SACM`," & _
"Data.`600 SACM`" & _
"FROM history.data " & _
"WHERE Data.Ubr='" & strUbr & "' AND " & _
"Data.Date=#" & dtFromDate & "# AND " & _
"Data.Date<=#" & dtToDate & "#"


With ActiveSheet.QueryTables.Add(Connection:=strConnPar am, _
Destination:=Range("A4"))
.CommandText = strSql
.PreserveFormatting = True
.AdjustColumnWidth = False
.RefreshStyle = xlInsertDeleteCells
.RefreshStyle = xlOverwriteCells
.Refresh BackgroundQuery:=False
End With
End Sub


Many Thanks

Gary Majdanek