DB functions with MS Access 2003

[fuzzbug]

I am trying to use the DB functions with MS Access 2003

I have a table called countries in a database called test

The table contains 2 records and 2 fields

The following script runs without errors but insists that the table contains zero records and zero fields. Why?


DBConnect>Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\test.mdb;User Id=admin;Password=;,dbEU3
DBQuery>dbEU3,select * from countries,tableCountries,NumRecs,NumFields,0
Message>records %NumRecs%
Message>records %NumFields%

[Marcus Tettmar]

Is your connection string correct? What does DBConnect return? Should the user ID really be admin, but the password blank? Feel free to send your .mdb to support so that we can try it ourselves.

[fuzzbug]

I decided going straight to Access was a bridge to far.

But, weirdly, I have the same problem with Excel as I do with Access.

I have an excel table called excel_Table.xls
Sheet1 contains:
tag name
ENG England
FRA France
(in cells A1 to B3)

The macro below runs without errors but is adamant that Sheet1 contains zero records and zero fields!

Code: Select all

Let>connStr=Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=%SCRIPT_DIR%\Macro_Testing\excel_Table.xls;DefaultDir=%SCRIPT_DIR%\Macro_Testing
DBConnect>connStr,dbCountries
Let>SQL=select * from [Sheet1$]
DBQuery>dbCountries,SQL,tableCountries,numRecs,numFields,0

Message>numRecs
Wait>0.5
Message>numFields
Wait>0.5

PS I also tried using:
Let>SQL=select * from [Sheet1$A1:B3]

but still no joy

[Marcus Tettmar]

As per my last response, feel free to send me your .xls file and I'll see if I get the same problem. Could be due to the data format. Or, since you're getting the same issue as you did with Access, perhaps there's a problem with your ADO/ODBC/MSOffice/Windows installation.

[fuzzbug]

Code: Select all

Let>connStr=Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=%SCRIPT_DIR%\excel_Table.xls;DefaultDir=%SCRIPT_DIR%
DBConnect>connStr,dbCountries
Let>SQL=select * from [countries!A2:B3]
DBQuery>dbCountries,SQL,tableCountries,numRecs,numFields

The above code returns numRecs=0 and numFields=0

excel_Table.xls contains one worksheet called countries

countries contains 6 active cells - A1 to B3:
tag name
ENG England
FRA France

[Marcus Tettmar]

Try replacing this:

Let>SQL=select * from [countries!A2:B3]

With this:

Let>SQL=select * from [countries$]

While Microsofts docs say unnamed ranges can be used I have not been able to make it work. Instead retrieve the whole sheet and simply ignore the data you don't want.

[fuzzbug]

no that did not work so I named the range and used the range like this:
Let>SQL=select * from [countries!country_names]

but the still the same: the script runs without errors but confidently reports that there are zero rows and zero columns

I have to say I am getting despondant.

Code: Select all

Let>connStr=Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=%SCRIPT_DIR%\excel_Table.xls;DefaultDir=%SCRIPT_DIR%
DBConnect>connStr,dbCountries
Let>SQL=select * from [countries!country_names]
DBQuery>dbCountries,SQL,tableCountries,numRecs,numFields

Message>numRecs
Wait>1
Message>numFields

[Marcus Tettmar]

I found that named ranges do not work (despite what Microsoft's documentation says). I could only get it to work by retrieving the entire sheet and specifying just the sheet name followed by $:

Let>SQL=select * from [countries$]

Or:

Let>SQL=select * from [sheet1$]

(Depending on the name of the worksheet)

That works for me. I have never got named ranges to work - it always returns nothing.

[fuzzbug]

I had already tried
Let>SQL=select * from [sheet1$]

but I just tried it again and it works now


thank you