Dao v's Ado Using a Query

[kennedymr2]

I am interested in a simple way of carrying out the following using ADO

I am using Access 2007.
InsertOneTimeInvoice is an append query, which has a lot of complicated formula's etc..
I am trying to avoid creating a massive sql statement, and just use the stored access 2007 query.

There seems to be several ways of doing it.!!
Is there an 'easy' way ..???

Current method....

Dim qdf As QueryDef
Set db = CurrentDb
Set qdf = db.QueryDefs("InsertOneTimeInvoice")
qdf.Parameters(0) = Invoice
qdf.Parameters(1) = order
qdf.Parameters(2) = overseas
qdf.Execute

Appreciate some advice

Kennedymr2

 

[jrbarnett]

CurrentDb.Execute "Insert into OneTimeInvoice (Invoice, Order, Overseas) Values '" & Invoice & "', '" & Order & "','" Overseas & "')", dbFailOnError

You will need to customise to your own target table and field names (rather than write to the query directly), I have just used those you member here.

John

 

[kennedymr2]

John... Thanks very much for your reply.

I was trying to avoid using a string.

The append query is complex and contains lots of calculations etc. If i convert it to a string, it would be 20 lines long , at least.

I am trying to get ado to actually use the stores access query and just 'execute' it.

Regards Kennedymr2

 

[jrbarnett]

Not really - using ADO to connect will also require setting lots of properties and connections, around the same as DAO (as with your existing code).

Question: Does the query store data within Access (either locally or via linked tables) or within a remote database server (SQL Server, Oracle, MySQL etc)?
There may be benefit to using ADO if the latter is true.

John

 

[kennedymr2]

Ok... Thanks John....

I will have to leave it as DAO....

DAO will probably outlive the program anyway ??!!!

Appreciate you interest and advice.


Regards Kennedymr2

 

[PHV]

DAO is still the preferred way to access any JetSQL datasource.