ADODB and DAO Libraries Conflict

[Bre-x]

On my Access App, I have a confict between the
DAO 3.6 Library and Activex Data Object 2.1

Is there a way to change this function to a DAO? or anything else that will
not conflict with it?


Public Function my_conn(sql As String)
Dim cn As New ADODB.Connection
Dim cmd As New ADODB.Command
cn.ConnectionString = "DSN=cnc"
cn.Open
cmd.ActiveConnection = cn
cmd.CommandText = sql
cmd.Execute
cn.Close
Set cmd = Nothing
Set cn = Nothing
End Function

 

[Douglas J. Steele]

Can you elaborate on what you mean by "a conflict between the DAO 3.6
Library and Activex Data Object 2.1"?

There should be no problems using both of those libraries in the same
application.

You're missing a Set in your code. You'd also be better off using:

Public Function my_conn(sql As String)
Dim cn As ADODB.Connection
Dim cmd As ADODB.Command

Set cn = New ADODB.Connection
Set cmd = New ADODB.Command

cn.ConnectionString = "DSN=cnc"
cn.Open

Set cmd.ActiveConnection = cn
cmd.CommandText = sql
cmd.Execute

cn.Close
Set cmd = Nothing
Set cn = Nothing
End Function

(and, of course, sql must be an action query: INSERT INTO, UPDATE, DELETE)

 

[Bre-x]

Thank you for answering my post.

My application was working find, no problem sending sql queries to my ODBC
connection.

The problem started when I create a new function. This new function gives me
error: Run-time error 13 Type mismatch

If I remove the Activex Data Object Library or copy the function an run it
on another app it works fine.

Public Function import_text_table()
On Error GoTo E_Handle
Dim intFile As Integer, strInput As String, sql As String
Dim db As Database, rs As Recordset

intFile = FreeFile
Set db = DBEngine(0)(0)
thepath = "N:\1CNC\ACCESS\MACHINES\625\6252018.txt"

Set rs = db.OpenRecordset("Local_importtext") 'ERROR IS HERE
Open thepath For Input As intFile
Do
Line Input #intFile, strInput
rs.AddNew
rs!Field = strInput
rs.Update
Loop Until EOF(intFile)

rs.Close
Set rs = Nothing
Set db = Nothing

sExit:
On Error Resume Next
Reset
rs.Close
Set rs = Nothing
Set db = Nothing
Exit Function
E_Handle:
MsgBox Err.Description & " " & Err.Number
Resume sExit
End Function

 

[Douglas J. Steele]

The reason for the error is because you're not disambiguating your
declarations. There's a Recordset object in both the ADO and DAO models. To
ensure you get the correct one, you need to disambiguate:

Dim rs As ADODB.Recordset

or

Dim rs As DAO.Recordset

The list of objects with the same names in the 2 models is Connection,
Error, Errors, Field, Fields, Parameter, Parameters, Property, Properties
and Recordset, however I even disambiguate objects such as Database.


Dim db As DAO.Database, rs As DAO.Recordset

 

[Bre-x]

Thank you very much. It fix the problem.

Bre-x

 

[David W. Fenton]

(and, of course, sql must be an action query: INSERT INTO, UPDATE,
DELETE)

Not true in ADO -- you can execute a SELECT.

 

[Douglas J. Steele]

Not true in ADO -- you can execute a SELECT.

While I agree that you can execute a SELECT statement, given that the
routine in question was using the Execute method of an ADO Command object
and wasn't creating a recordset, what would be the point of executing one?

 

[David W. Fenton]

While I agree that you can execute a SELECT statement, given that
the routine in question was using the Execute method of an ADO
Command object and wasn't creating a recordset, what would be the
point of executing one?

I dunno! I never understood the utility of allowing Execute to
accept SELECT statements, myself, but I never really cared about ADO
in the first place (and still don't, of course).

 

[Douglas J. Steele]

I dunno! I never understood the utility of allowing Execute to
accept SELECT statements, myself, but I never really cared about ADO
in the first place (and still don't, of course).

Not that I'm a big user of ADO either, but the Execute method can be used to
return a recordset:

Set recordset = command.Execute

as opposed to simply

command.Execute

 

[Rod Plastow]

Not that I'm a big user of ADO either, but the Execute method can be used to
return a recordset:

Set recordset = command.Execute

Used that one a lot in my ADO days. Only way I discovered of using
parameter queries. E.g.

m_cmdGetTx("TxTypeId") = frTxTypeTricHire
m_cmdGetTx("DateLow") = dteEarliestOpen
m_cmdGetTx("DateHigh") = Date
Set rstTx = m_cmdGetTx.Execute

Rod