Code Not Working On New Computer 1

[shelby55]

Hello

I have an Excel workbook that always worked before but giving me an error message now: "run time error 1004 - general odbc error".

The code is (provided by Skip):

Private Sub Worksheet_Activate()
    Dim sConn As String, sSQL As String, sPath As String, sDB As String
    
    sPath = ThisWorkbook.Path
    
    sDB = Split(ThisWorkbook.Name, ".")(0)
    
    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ".xls;"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

    With ActiveSheet
        With .QueryTables(1)
            .Connection = sConn
            Debug.Print .CommandText
            .Refresh False
        End With
        .Calculate
    End With
End Sub

The code is halted at .Refresh False.

This workbook uses MS Query from data on another page to summarize data for this page. I recently upgraded to another computer.

Thanks for any assistance.

 

[SkipVought]

hi,

What is the file extension of your new workbook? Is this 2007+?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

anyhow, try this...

[b]
    sDB = ThisWorkbook.Name[/b]

   
    sConn = "ODBC;DSN=Excel Files;"[b]
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"[/b]
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks for the reply but it didn't work.

The worksheet was created in Excel 2003 which I can still open it up as (I have 2003, 2007 and 2010 loaded on my new computer) but I still get this error.

I should have indicated that my new computer is Windows 7 where my old one was Windows XP. My new computer is also 64 bit. I've been reading up on ODBC connections and it seems that there may be an issue with 32 to 64 bit...but I don't know how to correct it.

Anything else to try? Thanks.

 

[shelby55]

P.S.
If I click on "edit query" for the MS Query then I get the error message "ODBC Excel Driver Login Failed: unrecognized database format f:/myfiles/myworkbook.xls." If I indicate okay it still takes me to select a workbook so I do, indicate okay and get an error message "this query cannot be edited by the Query Wizard". I indicate okay and get the error message "Microsoft Query: too few parameters. Expected 12."

 

[SkipVought]

in your 2007+ version...

1. Data > Connections > Properties

2. in the Extended Data Properties dialog, open the Connection (icon on right)

3. in the Connection Properties, select the Definition TAB

Please COPY and POST the text from these two text boxes:

1) Connection string:
2) Command text:



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip!

Connection String:

DSN=Excel Files;DBQ=F:\Database\MH MIS\2011 12\Apr 16 2012\Counselling Centre Stats 2011 12.xls;DefaultDir=F:\Database\MH MIS\2011 12\Apr 16 2012;DriverId=790;MaxBufferSize=2048;PageTimeout=5;

Command text:

SELECT `RawData$`.`Referral Received Date`, `RawData$`.`Date Assessment Completed`
FROM `RawData$` `RawData$`
WHERE (`RawData$`.`Referral Received Date` Is Not Null And `RawData$`.`Referral Received Date`>{ts '2009-03-31 00:00:00'})

 

[SkipVought]

so is this the correct path\name for your workbook, and your workbook is a '97-2003 workbook, and the F Drive is mapped correctly?
[tt]
F:\Database\MH MIS\2011 12\Apr 16 2012\Counselling Centre Stats 2011 12.xls
[/tt]
You might try using the 2007 driver
[tt]
DriverId=1046
[/tt]
replacing 790 in the Connection String.


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Thanks Skip...I always learn so much from you!!

But my problem isn't fixed yet...I actually have many queries in my workbook (one per sheet) and though the first was correct (and as per above), some of the others contain an incorrect connection string pointing to an incorrect file and folder.

However, when trying to edit this I get an error message of "[Microsoft][ODBC Excel Driver] too few parameters. Expected 12."

How do I fix this? Thanks.

 

[SkipVought]

Paste this is the Worksheet Code Sheet for each sheet containing a QUERY...

Private Sub Worksheet_Activate()
    Dim sConn As String, sSQL As String, sPath As String, sDB As String
    sPath = ThisWorkbook.Path

    sDB = ThisWorkbook.Name

    sConn = "ODBC;DSN=Excel Files;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=790;MaxBufferSize=2048;PageTimeout=5;"

    With ActiveSheet
        With .QueryTables(1)
            .Connection = sConn
            Debug.Print .CommandText
            .Refresh False
        End With
        .Calculate
    End With
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

The code you provided is exactly what was there except for the sDB = ThisWorkbook.Name and replacing the & ".xls;" with just "," which I had changed as per your earlier instructions.

I still went ahead and did as you requested (replacing each worksheet code with above) but still get the Run-Time error 1004 - general ODBC error.

 

[SkipVought]

On ANY sheet that this error occurs, perform the same steps posted on 17 Apr 12 11:04.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Also, something else that isn't working is that the command brings over two columns of dates "referral received" and "date assessment completed" but now the second column (date assessment completed) is coming over as the serial date BUT the other formulae using this date for calculation of days aren't recognizing it as a date so the days calculation is incorrect.

Do you know why this would occur? Thanks.

 

[SkipVought]

oops: replace 790 with 1046, as suggested before!!!

ALL REAL DATES are just NUMBERS, which can be formatted in any number of desired date formats.

If the range is formatted GENERAL and you still see a "date" then you have TEXT, not dates!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

Even before you responded I changed the 790 with 1046 and it still isn't working.

I know what you mean about it just being a format and I checked the data type and it is date. The formulae that are no longer working:

SumProduct((dts>D$1)*(dts<E$1)*(dts>=If($A2="",Today(),$A2)+1)*(dts<=IF($B2="",Today(),$B2)))

Note that dts is a named range for dates for the entire year, D1 is the end of month date(4/1/2011), A2 is the referral received date and B2 is the date assessment completed date. The purpose of the formula is to show # of days that were in April. So if referral received was April 1, 2011 and Date Assessment Completed was April 15,2011 then 14 days in April; if the Assessment date was May 15 then there would be 29 days in April and 15 in May.

Just as a test, I changed the serial date to a date i.e. typed it in as 4/1/2011 and the formula worked again so why would that be?


Thanks.

 

[SkipVought]

a value that is TEXT can have a FORMAT that is DATE and NOT BE A DATE since it is TEXT.

The ONLY way to know, is to change the FORMAT to GENERAL for the range that contains your "dates" and then you should see NUMBERS.

If the display is all NUMBERS, then change the format back to DATE.

Otherwise you have an issue to correct.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Also, do ANY of your queries work?

Please post the CONNECTION string and COMMAND TEXT string for each query that does NOT work.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Thanks Skip...but what issue? These imported via query fine before so not sure why not now. Not sure if this is helpful but in order to create in the format I wanted I used the following formula on the numbers: date(year(B2),month(B2),day(B2))) and it showed as date so doesn't that prove it's a date field?

 

[SkipVought]

???
[tt]
date(year(B2),month(B2),day(B2)) IS EQUAL TO B2
[/tt]
???

What do you mean by, "order to create in the format I wanted?"

A DATE is a DATE, format not withstanding!

a DATE is a NUMBER!!! That is ALL! Nothing more; nothing less!

The date FORMAT is simply a human interface display feature! Nothing more; nothing less!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[shelby55]

Hi Skip

I am aware of the principles of data but not understanding why this data is acting as it is.

The column is importing as a number instead of in the date format specified in the query. So instead of 4/22/09 it shows as 39925. I realize that this should make any difference to the formula because, as you note, a date is a date.

When I select on the column it indicates it is a date; when I change format column to general it stays as a number. But when I try to change the format, it won't change. I've tried to change by using the format painter and also using format cells and date format. The only other thing to note is that within the "type" window for date it is showing as *3/14/01 with the asterisk meaning that it responds to changes in regional date and time settings as specified for the OS. But trying to change it to another format from this list produces no changes.