ADO Connection

[AlexanderBB]

I have a connection between Excel and Access which is/was working fine, so I thought, but I've just discovered my recordset items are no longer than 256 characters.

Please don't tell me this is a max limit, and much of my stuff is longer.

My connect string is

cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & theName & ";" & _
"extended Properties=""Excel 8.0;HDR=Yes;IMEX=1"";"

and Cursor types

rx.Open sql$, cnn, adOpenStatic, adLockReadOnly

Can I get round this anyhow - please? !

Thanks, ABB

[AlexanderBB]

Ok I've done a bit more work on this and can maybe improve the question. (Assuming this is the right Forum for it ??)

If I connect to Access, from Excel, I can apply a query and the resulting recordset elements will contain all the data. (>255 chars if the Access table field is Memo)

However, the reverse - Access connecting to Excel has a limit of 255 characters.

These are my findings. Am I right so far ?

Is there any way for Access to get > 255 chars from Excel ?

Thanks for any answers, ABB

[techgnome]

let's back up for a moment... how are you verifying that you're only getting 256 characters?

In other words, how are you checking your data?

-tg

[AlexanderBB]

Hi techgome,

In the debug window in Access. I print rx(0) and check it against what's in Excel.
I also pasted it into ? len("....") to get the actual count.

[dmaruca]

This problem comes into play because the ODBC driver only checks the first 8 or so rows of data, and if none of those rows are > 255 then the field is created with that restriction. There is a fix for it.

http://support.microsoft.com/kb/189897/

I personally don't find connecting to excel sheets that way very appealing.

[dmaruca]

More real good info.

http://www.xtremevbtalk.com/showthread.php?t=217783

[AlexanderBB]

Hi dmaruca,

Intriuging read and nicely imformative. Thanks for the links. It's given me some ideas.

I'm going to apply the registry change as outlined but I'll be surprised if it works. If DWORD specifys
the number of rows to check and max is 16, and you don't have one cell with >255 in the first 16 rows.... no change? It does recommend NOT using 0 to check all rows.

I'm tempted to force one to > 255 and see what happens. The best thing is Excel can read everything from Access, so the problem is just one-way.

>don't find connecting to excel sheets that way very appealing

I don't know any other way. But this is working and if this 255 limit can't be got around, I'll get the data into Access soeme other way !

Regards, ABB

[dmaruca]

The preferred method would be to either use docmd.transferspreadsheet from access or open the workbook using excel automation and assign the data to an array like so

arrayVariant = sht.Range("A1:Z100").Value2

use whatever range suits you. arrayVariant is now a 2 dimensional array that you can feed into anything you want. Cheers.

[AlexanderBB]

dmaruca I don't know how to open "using excel automation' .
Had a try with docmd.transferspreadsheet and recieved Run-time error '3170':
Couldn't find installable ISAM.

Googled for a fix for this and found dozens of people asking but not ONE answer or solution. Isn't that soooo annoying!!?? It must be on here somewhere - off to search more!

[dmaruca]

Using excel automation means opening up an Excel.Application and controlling it.

http://support.microsoft.com/kb/219151

The ISAM error could be related to the argument you supplied to the spreadsheet type (2nd paramater).

http://support.microsoft.com/kb/90111

I couldn't provide any more help without more specific questions or examples of what you're doing.

[AlexanderBB]

Fair enough, thanks dmaruca but I'm just about ready to flag it! There's nothing that says what 'automation' is, without that knowledge the article can be read but is meaningless. Microsoft Excel 11.0 Object Library is ticked in the References FWIW.

It's unclear if the ISAM error is a result of Excel or Access. Msexcl40.dll is present and it's path is correct in the Registry. Running "Regsvr32 c:\Windows\system32\msexcl40.dll" reports it suceeded. But the error remains. It does compile without complaint.

The next suggestion was to re-install. But reinstall what ?

There's just the one line in Access (97) and Eccel is Ofice 2003.

DoCmd.TransferSpreadsheet acImport, , "tblXferTest", "E:\00Latest\ADO999886mw.xls", True, "A1:G12"

I still intend to try a 'dummy' cell of > 256 chars and see what happens with the ADO connection.
Regards, ABB

[dmaruca]

Automation is just a term for controlling excel using code. that article gives perfect example of that.

I can't speak any further about the ISAM error since I don't have 97. Try saving your workbook as an earlier version and try that. Other than that...

[AlexanderBB]

I open Excel and have various macros that are used. Is that 'automation' ? The article kicked off with 'binding' and how there are two kinds.... so I was a bit lost ! But anyway your DoCmd.TransferSpreadsheet method looks more promising. Whan you said you didn't have 97 I remembered I have Access 2003 (but am more familiar with 97) so I tried the command in that and it almost worked. No ISAM Error but now there's Run-time error '2391':

Field 'F1' doesn't exist in destination table 'tblXferTest.'

Which is quite true but it doesn't exist in Excel either. And setting 'HasFieldNames' to either True or False doesn't fix it.

This seems to conflict with MS saying set it True 'to use the first row of the spreadsheet as field names'. The first Excel row does have Field Names and they're the same as the Field names in Access.

Must you rename all the Access Fields to F1, F2, F3 etc? And I suppose after the transfer you could rename them back, but that seems silly.

Do you get the same problem ?

Thanks, ABB

[dmaruca]

The transferspreadsheet method definitely isn't perfect It looks like it's trying to pick up junk data.

One way around that is to transfer it into a new table, append the columns you want into your existing table, and then to delete the new table.

If you want you can zip up your spreadsheet and post it and I'll help you get it input if nothing seems to work. Good night!

[AlexanderBB]

I open Excel and have various macros that are used. Is that 'automation' ? The article kicked off with 'binding' and how there are two kinds.... so I was a bit lost ! But anyway your DoCmd.TransferSpreadsheet method looks more promising. Whan you said you didn't have 97 I remembered I have Access 2003 (but am more familiar with 97) so I tried the command in that and it almost worked. No ISAM Error but now there's Run-time error '2391':

Field 'F1' doesn't exist in destination table 'tblXferTest.'

Which is quite true but it doesn't exist in Excel either. And setting 'HasFieldNames' to either True or False doesn't fix it.

This seems to conflict with MS saying set it True 'to use the first row of the spreadsheet as field names'. The first Excel row does have Field Names and they're the same as the Field names in Access.

Must you rename all the Access Fields to F1, F2, F3 etc? And I suppose after the transfer you could rename them back, but that seems silly.

Do you get the same problem ?

Thanks, ABB

[westconn1]

@abb, see tutorial for automating office applications, including excel
in the faq thread at the top of this forum

set oexcel = createobject("excel.application")
set wb = oexcel.workbooks.open("somepath\workbook.xls")

you can now work with the wb object, though often more useful to have sheet object

set sht = oexcel.workbooks.open("somepath\workbook.xls").sheets(1)  ' sheet index or name
arr2d = sht.range("a1:f99")

arr2d is a 2 dimensional 1 based, variant array and would contain the values of all the cells in the specified range
you can then use the array to populate records in access
if finished with excel

wb.close false ' not to prompt to save changes and discard
set wb = nothing
oexcel.quit
set oexcel = nothing

nearly all excel macro code can be adapted to work in automation from other applications, but be extra careful if trying to work with selection object