Any Omnis DB People Here?

[Sam050507]

Hi,

I'm trying to tie into a new Omnis database here at work with VB6 via an ADODB/ODBC connection.

I can connect fine and pull down records until I need to use the "WHERE Date=#10/10/2006#" clause.

I was hoping somebody here would be familiar with the peculiarities of this database SQL syntax.

Thanks

 

[BobRodes]

Nope, but you could try entering the date as a string and seeing if it will fix it up for you:

Date = '10/10/2006'

You could also try using the convert function to convert the string to a date, if that doesn't work.

 

[JoeAtWork]

What I've found is that you have to use syntax that ADO expects, which is not necessarily what the source database uses.

For example, I've found that when I connect to Access with ADO the * wildcard does not work, I need to use % instead.

I guess internally ADO maps it's syntax to the database. The reason is probably that ADO code was meant to be easily ported to different databases. I.e. if you changed your database, the only thing you needed to change in the code was the connection string.

So far I've found that T-SQL syntax matches ADO.

 

[BobRodes]

<I guess internally ADO maps it's syntax to the database.

That's nearly accurate, but more accurately, OLE DB itself does this.

OLE DB is a framework of COM classes, part of which handles data chores. It does so in terms of consumers and providers; ADO is an example of an OLE DB consumer (JET is an example of an OLE DB provider; note the Provider=JETx.x segment in the ConnectionString property). An OLE DB consumer is responsible for expressing a request in terms of set of OLE DB method calls, and an OLE DB provider is responsible for processing a set of OLE DB method calls by returning or manipulating data. So, it's more accurate to say that various OLE DB objects broker ADO requests and database responses to those requests, rather than that ADO handles that brokering itself.

For massive detail, check the OLE DB reference, for example

http://msdn.microsoft.com/library/d...s/oledb/htm/oledbgetting_and_setting_data.asp

HTH

Bob