Problem with SQL and ADODB.Connection.

[theObserver]

I have an annoying problem with SQL in VBA. I've setup a ADODB.Connection to run sql against a worksheet using referenced cells :-

Public Function OpenExcelDatabase() As ADODB.Connection
Dim sTemp As String

Set cn = New ADODB.Connection

If Val(Application.Version) < 12 Then 'Excel 97-2003
sTemp = "Provider=Microsoft.Jet.OLEDB.4.0;"
Else 'Excel 2007-2010
sTemp = "Provider=Microsoft.ACE.OLEDB.12.0;"
End If

sTemp = sTemp & "Data Source=" & ThisWorkbook.FullName & ";" & "Extended Properties='Excel 8.0;HDR=yes;IMEX=1'"
cn.ConnectionString = sTemp
cn.Open

Set OpenExcelDatabase = cn
End Function

Problem is, things work fine when my where clause contains a number. But when I change it to a letter, I get a 'no value given for one or more requirement parameters' or 'automation error' :


Set DBConnection = OpenExcelDatabase
Set rngSource = Sheets("Mastercard CMTC Combinations").Columns("E")

sWorksheet = "[" & rngSource.Parent.Name & "$]"

sSql = ""
sSql = sSql & " " & "SELECT DISTINCT [CAM]"
sSql = sSql & " " & "FROM " & sWorksheet
sSql = sSql & " " & "WHERE [CIC] = B"
MsgBox (sSql)
PopulateControl cmbCM1, sSql, DBConnection

****
Public Sub PopulateControl(Ctrl As ComboBox, sSql As String, ByVal cn As ADODB.Connection)
Dim rs As ADODB.Recordset

Set rs = New ADODB.Recordset
Set rs = cn.Execute(sSql)
....
***
If I change the 'B' in the where clause to a '2', things work fine. The column [CIC] contains 2s, Bs etc.

Any idea what could be causing the problem? I've tried "WHERE [CIC] = B" and "WHERE [CIC] = 'B' "

 

[Jerry Sullivan]

Hi theObserver,

See this thread regarding how mixed data types are handled through ADO.
http://www.mrexcel.com/forum/microsoft-access/625668-ado-connection-excel.html

It sounds like your problem is related to ADO interpreting your CIC field as being a numeric data type. This can happen if TypeGuessRows is set to the typical default value of 8 and your first 8 rows of data in that field are numbers.

If that's the cause, I would expect your WHERE clause to error on either [CIC] = 'B' or = [CIC] = '2' but work on [CIC] = 2

If you opt change your registry to the settings Colin suggested, the drivers might be in a different location that what AlexanderBB found on his computer.

On my 64 bit computer running 32 bit Excel 2010, the key is located here...

Hkey_Local_Machine\Software\Wow6432Node\Microsoft\Office\15.0\Access Connectivity Engine\Engines\Excel\TypeGuessRows

Rory's suggested approach could be more robust if you are going to provide your application to other users with unknown registry settings.

 

[theObserver]

Thanks JS411. So if I ensure the first eight rows were a mixture of numeric and alphabetical characters, my problem should go away.

I'll try it tomorrow in work!

 

[Jerry Sullivan]

Yes, that should work too- provided TypeGuessRows is set to the default of 8 and ImportMixedTypes is set to Text

 

[theObserver]

Yep, it worked ! Initially I thought it didn't because I hadn't saved my worksheet after making the changes. Thanks for you help.

Here's some code that spits out the table name and the data type. Might be useful to someone reading this with the same problem :-

'Set TablesSchema = cn.OpenSchema(adSchemaTables)
'Do While Not TablesSchema.EOF
' Set ColumnsSchema = cn.OpenSchema(adSchemaColumns, Array(Empty, Empty, "" & TablesSchema("TABLE_NAME")))
'Do While Not ColumnsSchema.EOF
' Debug.Print TablesSchema("TABLE_NAME") & ", " & _
'ColumnsSchema("COLUMN_NAME") & ", " & ColumnsSchema("DATA_TYPE").Value
'ColumnsSchema.MoveNext
'Loop
'TablesSchema.MoveNext
'Loop

 

[Kyle123]

It's worth noting that also for anyone else doing this, that you shouldn't be querying an open workbook like that since it causes a well documented memory leak. You should either close the workbook to query it or use DAO rather than ADO - I haven't tried this last method, but I believe it works.