Simplify ADO connection/recordset

[firsttube]

I currently have my ADO connection set up as such:


Dim rs As New ADODB.Recordset
Dim sqlStat As String
Dim adoConn As New ADODB.Connection
Dim adoCmd As New ADODB.Command
Dim strConn As String, strSQL as String

'Open a Connection object
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
strConn = strConn & "c:\myMDB.mdb"
adoConn.ConnectionString = strConn
adoConn.Open

sqlStat = "SELECT * FROM Table"
rs.Open sqlStat, CurrentProject.Connection
strSQL = "INSERT INTO NEWTABLE VALUES (value1, value2)"

' Set up the Command object
adoCmd.CommandText = strSQL
adoCmd.CommandType = adCmdText
adoCmd.ActiveConnection = adoConn


Do until rs.eof
value1 = rs("FIELD1")
value2 = rs("FIELD2")

adoCmd.Execute
loop

rs.close
set rs=nothing
Set adoCmd = Nothing
adoConn.Close
Set adoConn = Nothing



My question is, can anyone simplify the ado connection? It seems kind of redundant.

thanks!

ft

Set the gearshift for the high gear of your soul, you've got to run like an antelope, out of control.

 

[Dimandja]

Where is the redundancy?

 

[firsttube]

well, the strConn seems redundant, as I will always be running this on the current open database, so why do I have to specify the provider and database path?

and instead of using the command object to update the table, can't I just use the recordset?



Set the gearshift for the high gear of your soul, you've got to run like an antelope, out of control.

 

[CajunCenturion]

You can execute an SQL statement directly from the Connection Object:

adoConn.Execute ("INSERT INTO tblReportMaster VALUES (" & Trim(Var1) & "," & Trim(Var2) & ",'" & Trim(Var3) & "'," & Trim(Var4) & ",'" & Trim(Var5) & "')")

If this is an Access project, then you may not even have to create a new connection to the database.

CurrentProject.Connection.Execute (<sql statement>)

This won't work with a SELECT statement as you need a place to store the results (the RecordSet), but INSERT, UPDATE, and DELETE statements don't need return values.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PHV]

And what about something like this ?
DoCmd.RunSQL "INSERT INTO NEWTABLE SELECT * FROM OldTable"


Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244