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

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

Hi Dick

Thanks for the response.

There have been no changes to the database. (No-one else know it's
there yet).

I have tried recreating the query via MS Query, with the recorder on. I
specified paramters via the use of square brackets, thus enabling the
Parameters button on the dialog box following the Return Data To Excel
option. I then changed these to reference a cell value (or three),
replicating the Name, From Date and To Date in my query.

Clicking the Finish button, causes Excel to crash. I think this was the
reason, I turned to a more VBA based solution in the first place - I
can successfully record queries with one parameter, but not more.

A simpler recorded query did not suggest anything amiss in the
Connection property.

I have one other clue.

As you suggested, there is an extant query table - the result of the
last successful execution. Two sub routines delete any query tables,
and the 'External Data' names created during the process. I agree that
a refresh option may be better, although I was a bit nervous due to the
crash problem described earlier. In any event, with the active cell in
the query table, I selected Edit Query. I received an error message
saying that the database table could not be found - it would appear to
be looking on my hard drive, despite the path being declared in the
connection string. Clicking through the error message, thereby reaching
MS Query, I noticed that, in the upper pane, where the tables are
shown, the title bar says external data, as opposed to 'Query from MS
Access database'

Again, any further assistance would be much appreciated

Gary Majdanek

Gary,
Nothing specific, but I generally stay away from using dubious words (e.g.
Date) in DB structures to avoid confusing the system. If you must use Date
as a field name, try enclosing it in [ ] brackets.
Likewise with spaces, unless you really need that clarity.

Can't say about the connection string, but the easiest to generate/test it
is to create a new file somewhere suitable and called it "Whatever.udl".
Then double click, follow the step and examine the file in a text editor.
Do a debug.print of strSql to check it evaluates to what you expect.

NickHK

wrote in message
oups.com...
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

Thanks Nick

Appreciate the field naming point - more inexperience with Access than
anything else.

Have recreated the query and have gone with Dick's refresh option,
which seems to be working.

Still find it frustrating though, that one minute it was working, the
next it wasn't. It's the sort of thing that will bug me even though I
seem to have an alternative.

Again, thanks for the reply

Gary