ADO & Recordset: Where is the recordset (or table) stored?

[ReViVo]

Hello,
I'm trying to execute the following code in a (Windows CE 2.11) palm size device (with ADOCE installed)

Dim rs '-- Variable to hold Recordset object
Dim strSQL As String '-- SQL Statement to create new table

'-- Create RecordSet Object
Set rs = CreateObject("ADOCE.Recordset&quot
'-- Create SQL Statement to create new table
'-- The table has 2 columns -
'-- 1) "NAME" which is a text field
'-- 2) "AGE" which is an integer field
strSQL = "Create Table " & strTable
strSQL = strSQL & " (name text, age integer)"
'-- Execute SQL Statement to create table
rs.Open strSQL, , adOpenKeyset, adLockOptimistic
'-- Close Recordset
'rs.Close
'-- Remove object from memory
Set rs = Nothing

Note in this code, I had to remove "rs.Close" section as it caused "operation requested by the application is not allowed if the object is closed" error.

After I execute the code, it works correctly, i.e. it creates the table but I don't know where the table is created and stored! There's no file with ".mdp" or ".cdb" (equivalent of .mdp in Windows CE environment).

However, the table is stored because, when I try to create a table with the same name, it gives this error "Table 'sd' already exists" (After I have created a table with the name 'sd') That proves that the tables are really stored somewhere but I cannot find them.

Where could they be located? I'm really confused.

Thanks in advance.

 

[ram1975]

Please use any one of them i.e. either rs.close or set rs = nothing. Using any one of them would solve your problem. If you don't want to open it again, better to use
set rs = nothing

 

[ram1975]

please check your code i think what you have given is wrong i.e. the following code will run smoothly.
rs.close
set rs = nothing

However, the other way around it will give the error what u said
set rs = nothing
rs.close

THen that error will generate in the statement rs.close.

 

[billchris]

The Sql statement that you are executing does not return any records. That's why the rs cannot be closed, it was never open. If Sql Server does not return anything to ADO, it doesn't need to open a recordset, so it won't. You should use a Command Object or the Connection.Execute method to execute your Sql statement and since there is no data in your table yet, don't expect anything back.

But, that's not the question you asked. Where is the table created? It's probably in a Sql Server managed database file, very similar to normal Sql Server. If you create a table or a stored procedure, you probably won't see any new files created on your CE device. Instead, whatever file that Sql Server is using as a data file will contain the definitions.

If you want to use your table, create another Sql statement that adds data to the table and execute it with a Command or Connection object:

insert into <yourTableName>(name, age) values('Bob Smith', 21)

And create another one to retrieve the data. You can use the rs.open... syntax to execute this statement.

select name, age from <yourTableName>

You might want to check the Books Online for Sql Server CE and read about how to create applications.

“I apologize for this long letter. I didn't have the time to make it any shorter” --Blaise Pascal