0

I am trying to import some data from a .mbd-file into Excel. I can copy the whole database into a spreadsheet, no problem there. I can also use a statement like this

sSQL = "SELECT HOEHE " & "FROM h_datei WHERE HOEHE >= 53 "

The problem arises when using WHERE on a field that has type other than int or float. (I don't have MS Access, but MDB Plus tells me the type for text fields in my .mdb is called 'Widestring')

I get this in a MsgBox from Excel: Run-time error '-2147217913 (80040e07)': Automation error

I have been googling and tinkering for quite a while, but I am stuck. Under is my VBA code. I don't know if this has to do with the provider (Microsoft.ACE.OLEDB.12.0) but I have tried a few others. I'm also thinking about unicode, as this database is from a german surveying program.

sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=" & _
            sPath & "Oskarb04.mdb"

sSQL = "SELECT HOEHE " & "FROM h_datei WHERE PNR >= 53 "
Set rsData = New ADODB.Recordset
Debug.Print sSQL
rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdUnknown
toygarden
  • 1
  • If it is a string then you wouldn't use >= (unless implicit conversions supported and applicable). Do you mean Where x 'Like' ? Or even '=' ? What are some of the example items in this field? – QHarr Mar 08 '18 at 09:18
  • I could select some of the fields containing strings now, i had forgotten quotes! But one problem persists: PNR is a column in the database that contains only integers (at least in this case) but the type is 'WideString'. I know theres a value 1169. `'SELECT hoehe FROM h_datei WHERE PNR = '1169'` returns nothing and `SELECT hoehe FROM h_datei WHERE PNR = 1169` gives me the automation error – toygarden Mar 08 '18 at 09:23
  • `sSQL = "SELECT hoehe, pnr " & "FROM h_datei WHERE PNR <= '1169' "` (with the smaller than sign) gives me the whole column – toygarden Mar 08 '18 at 09:30
  • try with an N before for unicode string – QHarr Mar 08 '18 at 09:32
  • `sSQL = "SELECT hoehe, pnr " & "FROM h_datei WHERE PNR = '309' "` No results. `sSQL = "SELECT hoehe, pnr " & "FROM h_datei WHERE PNR = 309 "` Automation error `sSQL = "SELECT hoehe, pnr " & "FROM h_datei WHERE PNR LIKE '309' "` No results. Column PNR is type widestring and contains the value 309 – toygarden Mar 08 '18 at 09:41
  • sSQL = "SELECT hoehe, pnr " & "FROM h_datei WHERE PNR = N'309'" ? I don't know if N needed before pnr. – QHarr Mar 08 '18 at 09:42
  • `sSQL = "SELECT hoehe, pnr " & "FROM h_datei WHERE PNR = N'309' "` Gives me automation error – toygarden Mar 08 '18 at 09:44
  • Try some sort of cast on the column ? sSQL = "SELECT hoehe, pnr" & "FROM h_datei WHERE StrConv(PNR,128) = '309' ? see here https://support.office.com/en-us/article/strconv-function-19c3816f-dbc9-4adf-891c-fd32734c92e0 – QHarr Mar 08 '18 at 09:52
  • Also, worth keeping pnr as pnr in both cases not PNR but that is cosmetic. – QHarr Mar 08 '18 at 09:58
  • I tried the casting you described, but it still doesn't give me any data. – toygarden Mar 08 '18 at 14:20
  • I know converted the field type to number in MS Access, and it works. But it's not a good workaround, i need it to be seamless from excel, and besides the column wil hold strings sometimes. I can not find out what i'm doing wrong. MS Acces says this about the fields: IME-mode : No control IME-sentencemode : no conversion Textformat : Plain text This is translated from norwegian – toygarden Mar 08 '18 at 14:39
  • 1
    Aha! Leading spaces in the strings! sSQL = "SELECT pnr, hoehe " & "FROM h_datei " & _ "WHERE pnr = ' 100001'" This works, but it's different lenghts on the strings. LTRIM(pnr) worked! – toygarden Mar 08 '18 at 15:02
  • post as answer so others will be able to see :-) – QHarr Mar 08 '18 at 18:07

1 Answers1

0

For string comparisons you would normally use the 'Like' operator e.g.

SELECT column1, column2, ...
FROM table_name
WHERE columnN LIKE pattern;

You can combine this with Wildcards to get partial matches. An example from that link:

Wildcards

For an exact match you can use "="

SELECT column1, column2, ...
FROM table_name
WHERE columnN = 'myString';

If something like ">=" works on a string field then I suspect that field has been incorrectly set, I am guessing as unfamiliar with the software in question,and/or, an implicit conversion is able to be performed which incurs performance overhead and is susceptible to breaking.

If it is a unicode string, which a quick Google seems to imply a widestring datatype is, then

WHERE columnN = N'myString'

Failing that you can always try casting the column to a different type e.g.

StrConv(PNR,128) = '309'
QHarr
  • 83,427
  • 12
  • 54
  • 101