sfaust


I am trying to import several queries into excel based on an ODBC database. All I did was record inserting the queries with a macro and changed the path to a variable so that I could change the path of the mdb file. The reason is that we will have an mdb file per project and all mdb files will have the same tables and parameters with different values. Anyway, I have probably 30 queries to insert, and I have a macro that does this sucessfully for about half of them. There shouldn't be any difference because they are all set up the same way, but it runs about half way and gives me an "error 1004 - General ODBC Error."

The really strange thing is that I tried to delete the queries that seemed to be giving me trouble and re-running the macro, and when I had deleted the original ones, it started giving the same error on ones that had previously run sucessfully. Anyone know what's going on

I'm fairly new to this, so if it's really obvious go easy Smile

below is an example of one of the queries. I can post however much is necessary, so please say if you want to see more...

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=" & mdbpath & ";DefaultDir=C:\COSTS;Driver={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS Access;MaxB" _
), Array( _
"ufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;" _
)), Destination:=Range("B2"))
.CommandText = Array( _
"SELECT Areas.AreaType, Areas.Name, Area*3.2808419*3.2808419 AS 'Area', Areas.COSTPERSF, COSTPERSF*Area*3.2808419*3.2808419 AS 'Total Cost' FROM `" & mdbnoext & "`.Areas Areas WHERE (Areas.AreaType Like 'Gross%') " _
, "ORDER BY Areas.Name")
.Name = "Area"
.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





Re: ODBC import to Excel

sfaust


ok, I'm dumb. what happened was that in some cases the file path broke into two pieces of the array when it recorded, so when I replaced the original recorded path with the variant holding the new path, it didn't recognize those. I fixed that and it is working now. I still have 1 query that I can't figure out yet, though...

sorry







Re: ODBC import to Excel

Riquel Dong ¨C MSFT

Hi sfaust,

Welcome to MSDN Visual Basic General Forum, this forum supports Visual Basic.NET (7, 7.1, 8, ...) issues only, so your VBA question is an off-topic here and this isn't the best place to ask it. Please post your question to Visual Basic for Applications (VBA) to get an answer about your question.

Besr regards,

Riquel








Re: ODBC import to Excel

sfaust

Ok, thank you






Re: ODBC import to Excel

Cringing Dragon

There are some limitations in VBA that can be quite restictive at times, and especially so for writing queries.

I'm assuming Excel 2003, but I think these comments apply regardless of version.

The one you're most likely encountering is the string length limitation.

A text string can only hold 255 characters. Any characters in excess of that are truncated. So if you have (for example) your connection string in one text string, and it exceeds 255 characters in length, then VBA will only pass the first 255 characters - which will be an invalid connection string. The workaround for this is to use an array of strings instead of a single string - you probably noticed that an array was recorded, but you may not have realised why. Note if you use an array of strings you need to be careful to make sure you have spaces either at the end of one string or before the next, otherwise when they are concatenated back together again you'll likely get errors.