Hi prd00,

You need to change your logic to:

Establish the ADO connection during the form_load event,

Release the ADO connection during the form_unload event,

And use an "ADODB.Command" to do the actual work.

Dim lclCM as ADODB.Command
Dim lclRows as long

With lclCM
.ActiveConnection = YourADOConnection
.CommandText = YourQuery
.CommandType = adCmdText
.Execute lclRows
End With

lclRows will contain the number of rows affected by your query.

You can find more details in the MSDN website under "converting RDO to ADO".

Good Luck,

Thx for help.

I am using the CSV for import data from outside source into the database. I am just trying to use ODBC DSN file to connect. Right now I am using my own importing routines, but I plan to read the file using ODBC.
Create New DSN, use Text object. That's all.

Hi Bruce, it's not working.... Still the same....Okay.. this is my module...
I am using class for database, with Connection object shared for the whole class. The connection is made with clsDB.Open <Database Name> not at initialize. There are 2 main command at this class, clsDB.Execute and clsDB.Retrieve.


Private connADO as ADODB.Connection
Private Connected as Boolean

Public Sub Open(DBName)
cnString = "Provider=SQLOLEDB.1;Password=password;" & _
"Persist Security Info=True;User ID=user;Initial Catalog=" & _
"DBName & ";Data Source=SERVER"
If Not Connected Then
Set connADO = New ADODB.Connection
connADO.Open cnString
Connected = connADO.State = adStateOpen
Else
cnADO.Execute "USE " & DBName
End If
End sub

Public Function Execute (SQLCmd) as integer
Dim RecAffected As Integer, cmdADO As ADODB.Command
Set cmdADO = New ADODB.Command
With cmdADO
.ActiveConnection = connADO
.CommandText = SQLCmd
.Execute RecAffected
End With
SQLExecute = RecAffected
end sub

Public Function Retrieve(SQLCmd) As ADODB.Recordset
Dim rsADO As ADODB.Recordset

Set rsADO = New ADODB.Recordset
rsADO.Open SQLCmd, connADO
Set SQLRetrieve = rsADO
End Function

Private Sub Class_Terminate()
connADO.Close
end Sub


The sample Procedure to call them is like this

Public sub TestSub
dim rsADO as ADODB.RecordSet, cDB as clsDB
Set cDB = new clsDB
set rsADO = cDB.Retrieve("SELECT ID, Name FROM TYPE")
cDB.Open "Data2002"
for x = 0 to 1
do While not rsADO.EOF
cDB.Execute "INSERT INTO Temp (Fld1, Fld2, Fld3) VALUES (a1, " & _
rsADO(0) & ", " & rsADO(1) & ")"
rsADO.MoveNext
loop
cDB.Execute "DELETE FROM Sales WHERE ID in (SELECT Fld1 FROM TEMP)"
cDB.Execute "INSERT INTO Sales SELECT * FROM TEMP"
cDB.Execute "EXEC RecalcAll"
set cDB = nothing
Next
end sub

Now.. the problem lies at the last 3 command. Like this....
1. Connect because of Open command
2. Retrieve
3. Doing Execute loop
4. disconnect, reconnect, doing DELETE
5. disconnect, reconnect, doing INSERT
6. disconnect, reconnect, doing EXEC
7. disconnect, reconnect, doing Retrieve
8. Doing Execute loop
9. disconnect, reconnect, doing DELETE
10. disconnect, reconnect, doing INSERT
11. disconnect, reconnect, doing EXEC

At step 2,3, and 8 it is not disconnected.

This might be a typo - but you are destroying the instance in the for next loop - it should be outside the for next loop. Why are you looping twice ? Make sure to destroy your recordset object as well. Also, how often is it disconnecting (every time) ? What is cnADO ? Shouldn't cDB.Open be called before you 'set rsADO' ?

Oh.. yes... that's type.. it should be outside...
The real one, it's not twice, it's as much as it needs to read all kind of data on the disk.

Sorry... cnADO is supposed to be connADO... another typo...

Yes... everytime... For example, if I have 6 files on the disk, then it is disconnected 6 times, at step 4,5,6,7,9,10,11 The only time its not disconnected is when it's looping inside do-loop

Yes, cdb.open supposed be before set rsado... that's another typo... :) Sorry.. a lot of typo there...
It's supposed to be like this:


Public sub TestSub
dim rsADO as ADODB.RecordSet, cDB as clsDB
Set cDB = new clsDB
cDB.Open "Data2002"
set rsADO = cDB.Retrieve("SELECT ID, Name FROM TYPE")
for x = 0 to 1
do While not rsADO.EOF
cDB.Execute "INSERT INTO Temp (Fld1, Fld2, Fld3) VALUES (a1, " & _
rsADO(0) & ", " & rsADO(1) & ")"
rsADO.MoveNext
loop
cDB.Execute "DELETE FROM Sales WHERE ID in (SELECT Fld1 FROM TEMP)"
cDB.Execute "INSERT INTO Sales SELECT * FROM TEMP"
Next
cDB.Execute "EXEC RecalcAll"
set cDB = nothing
end sub


The real code supposed to be:
1. For-Next loop supposed to be for x=1 to < "*.csv" files count
2. and typo-ed rsADO
set rsADO = cDB.Retrieve("SELECT ID, Name FROM TYPE")
supposed to read records from CSV file using ODBC. Right now it's running using my own CSV routines, not with "Do Until rsADO.EOF".
3. the csv filename will be the table name.