Excel 2010 pb getting data from Access db

[oprache]

I have a VBA macro running on Excel 2003 to import data from an Access database using ADO with a parametrized command.
Using the same code with Excel 2010 (MS Access is not running) and the same mdb file, the system goes to "non responding" mode and hangs forever. Tracing through the code, the problem occurs at the last step.
Again, no execution issue if I use my old machine with Excel 2003
Below is the code I use to connect to the database.
I do not understand what I am not doing right
Thanks in advance
Olivier

Code:

wsSheet.Range("A1").CurrentRegion.Clear
Set rst = New ADODB.Recordset
MyConn = "Provider=Microsoft.JET.OLEDB.4.0;" & "Data Source=z:\olivier.mdb"
Set cnn = New ADODB.Connection
cnn.Open MyConn
Set cmd = New ADODB.Command
cmd.CommandType = adCmdStoredProc
cmd.ActiveConnection = cnn
cmd.CommandText = "Parametrics"
Set param = cmd.CreateParameter
param.Type = adChar
param.Size = Len(sPO)
cmd.Parameters.Append param
cmd.Parameters(0).Value = sPO
Set rst = cmd.Execute

 

[xenou]

What kind of value is sPO? adChar looks funny to me as that's typically a single character (and therefore 1 or 2 bytes in size, depending on the medium) - I'd normally expect to see something like adVarWChar (you may be able to leave the size argument out for a text data type):

Try:
Set param = cmd.CreateParameter
param.Type = adChar
cmd.Parameters.Append param
Or:
Set param = cmd.CreateParameter
param.Type = adVarWChar (if the data type is not actually a single character)
cmd.Parameters.Append param


Of course you say this works with 2003 so basically it means I have no idea really - but still, I think we'd need to know more about your data types and the values you are using for the parameter, if nothing else to create a proper test case.

ξ

 

[oprache]

Hi,
Thank you for the reply. sPO is a text string, anywhere from 8 to 12 characters.
However changing from adChar to adVarWChar does not improve the situation when running from Excel 10, whether I include the .Size statement or not.
I may be having a deeper issue as a macro I run every week stopped working today whereas it did last week. The curious thing that tells me there is something wrong with my system is that the Access query called by my macro does not run from Access 10 (but of course runs fine from Access 2003 on my older PC).
Unfortunately I am currently the only one in our office with Office 10 so it is not easy to tell where the issue is coming from
Thanks anyway
Olivier

 

[xenou]

Access usually sets text parameters to a size of 255, so you could try:

Set param = cmd.CreateParameter
param.Type = adVarWChar
param.Size = 255
cmd.Parameters.Append param
Or:
Set param = cmd.CreateParameter
param.Type = adVarChar
param.Size = 255
cmd.Parameters.Append param

(The adVarWChar should support international encoding - I can't remember offhand if this is what I usually use here but I think this is it).

I honestly don't know how you had this working before with a Char data type for the parameter, if it's 10-12 characters in length. Char data is only one character by definition.
This chart shows the mapping: http://www.w3schools.com/ADO/ado_datatypes.asp

However, if your Access macros don't work then your probably do have more problems.