Status: Solved
Priority: Medium
Security: Public
Views: 1405
Last Modified: 2013-11-24

Select query for DBF table in Excel VBA throwing Type mismatch

Hi,

the below code is failing on the last line, it is throwing a type mismatch error between the MaintCause field and the 'Disaster' value.
MaintCause is a 50 character field so it shouldn't have a problem with comparing strings.
May it have something to do with the query needing to built special for a DBF?

Any help greatly appreciated!

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + root_directory + module_prefix + "\LINES\MAINT\; Extended Properties=DBASE III;"

c1SQL = "Select * from LnMt.dbf Where Maintcause = 'Disaster'"
rs.Open c1SQL, cn, , , adCmdText

Open in new window

AssetFX

Asked: 2011-02-22

Advertise Here

1 Solution

LVL 44

pcelbaCommented: 2011-02-22

It should not report such error.

You may try VFP OLE DB provider: http://www.microsoft.com/downloads/en/details.aspx?FamilyID=e1a87d8f-2d58-491f-a0fa-95a3289c5fd4

Connection string: "Provider=vfpoledb;Data Source=d:\MyDataDirectory\;"

http://www.connectionstrings.com/visual-foxpro

and post more details about the errors.

LVL 22

rspahitzCommented: 2011-02-22

Can you try the select statement directly in Access to see if you get the same error?

AssetFXAuthor Commented: 2011-02-22

Hi guys, sorry for the delay, in Brisbane and internet is not very stable at the moment.

Feel silly but I just tried recreating the table and it started to work, the table is created as a linked table to a application to MapInfo and I believe it may have been corrupt.

Thanks for advice though

AssetFXAuthor Commented: 2011-02-27

Be careful with MapInfo linked attribute tables

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Select query for DBF table in Excel VBA throwing Type mismatch

Join & Write a Comment Already a member? Login.

Featured Post