Hello,

I am trying to write code that will define the ODBC connection. No problem
there.
I have created a workspace and then defined the connection to use my user ID
and password (to allow other to access data otherwise not available).

My problem is that my SQL that works if I have a regular database object and
openrecordset object no longer works. Do I have to modify the SQL since I am
using a connection object in place of the database object?

The changes that I am aware of is that Access will append the database name
to the beginning of the table.field string ( example: myTABLE.fieldname now
looks like dbMYDATABSE_myTABLE.fieldname).

I have a condition in my SQL on a field that houses dates. In my code that
portion of the SQL looked like ..."(mydatabase_mytable.OOSDATE Date() )"
This worked just fine for the past few weeks. But now that I have defined
the connection and tit gives me a runtime error "3146 ODBC--Call Failed"


I have done a simple query on another database table and the connection
object worked fine.
Can anyone help?

Thank you

James,
If you are using Access, you can test the SQL in the new query in Access to
see if the syntax is correct and you get the correct results.
One thing I see is that you are using dates. Does it help if the dates are
surrounded by "#" and evaluate Date() before you execute the query. So I
would change your query to:
.....mytable.OOSDATE #" & Date() & "#"

If would if you post your connection string and code.

NickHK

"James McDowell" wrote in message
...
Hello,

I am trying to write code that will define the ODBC connection. No
problem
there.
I have created a workspace and then defined the connection to use my user
ID
and password (to allow other to access data otherwise not available).

My problem is that my SQL that works if I have a regular database object
and
openrecordset object no longer works. Do I have to modify the SQL since I
am
using a connection object in place of the database object?

The changes that I am aware of is that Access will append the database
name
to the beginning of the table.field string ( example: myTABLE.fieldname
now
looks like dbMYDATABSE_myTABLE.fieldname).

I have a condition in my SQL on a field that houses dates. In my code
that
portion of the SQL looked like ..."(mydatabase_mytable.OOSDATE Date() )"
This worked just fine for the past few weeks. But now that I have defined
the connection and tit gives me a runtime error "3146 ODBC--Call Failed"


I have done a simple query on another database table and the connection
object worked fine.
Can anyone help?

Thank you

Nick, Thanks for the reply. I kept on playing with the SQL and found that I
had to encase the date in single quotes. I am now stuck on another query. I
will submit a new post.

Thank you,
Jim

"NickHK" wrote:

James,
If you are using Access, you can test the SQL in the new query in Access to
see if the syntax is correct and you get the correct results.
One thing I see is that you are using dates. Does it help if the dates are
surrounded by "#" and evaluate Date() before you execute the query. So I
would change your query to:
.....mytable.OOSDATE #" & Date() & "#"

If would if you post your connection string and code.

NickHK

"James McDowell" wrote in message
...
Hello,

I am trying to write code that will define the ODBC connection. No
problem
there.
I have created a workspace and then defined the connection to use my user
ID
and password (to allow other to access data otherwise not available).

My problem is that my SQL that works if I have a regular database object
and
openrecordset object no longer works. Do I have to modify the SQL since I
am
using a connection object in place of the database object?

The changes that I am aware of is that Access will append the database
name
to the beginning of the table.field string ( example: myTABLE.fieldname
now
looks like dbMYDATABSE_myTABLE.fieldname).

I have a condition in my SQL on a field that houses dates. In my code
that
portion of the SQL looked like ..."(mydatabase_mytable.OOSDATE Date() )"
This worked just fine for the past few weeks. But now that I have defined
the connection and tit gives me a runtime error "3146 ODBC--Call Failed"


I have done a simple query on another database table and the connection
object worked fine.
Can anyone help?

Thank you