ADO Objects

[beercat]

Let me apologize in advance, if this is an easy thing that I should know by now...

A project that I'm currently working on is my first attempt at using ADO. All of my previous projects have utilized DAO. I was looking at the object model and am confused - how can I access the equivalent of the Tables Collection, Fields Collection, etc? I see it in the ADOX model but not in the ADO model. Do I have to use ADOX? And if so, what are the major differences between the 2 models?

My ultimate goal is to be able to create and drop tables on the fly, but the only way I know is to look through the Tables Collection for tables named [whatever I want to drop] and then if it's found, Drop the table. PLEASE help! Thanks a bunch everyone!

Brian

[Serge]

Yes, you're right, there's no Table collection in ADO object model. As for Fields collection......it does exist as part of the Recordset object.

------------------

Serge

Software Developer
Serge_Dymkov@vertexinc.com
Access8484@aol.com
ICQ#: 51055819 (http://www.icq.com/51055819)

[beercat]

Well, if there is no Tables Collection, how can I Add and Drop tables in my VB code?

[Serge]

You can use SQL statements to do that.


Dim cn As New ADODB.Connection

cn.Open "MyDSN";UID=MyLogin;PWD=MyPassword"
cn.Execute "Drop Table MyTable"


To create a table you can use Create Table SQL statement:


Dim cn As New ADODB.Connection
Dim strSQL As String

cn.Open "MyDSN";UID=MyLogin;PWD=MyPassword"
strSQL = "CREATE TABLE ThisTable (FirstName TEXT, LastName TEXT)"
cn.Execute strSQL



Assuming that this database is an Access database. If you use SQL Server or Oracle database change the Text data type to VarChar

------------------

Serge

Software Developer
Serge_Dymkov@vertexinc.com
Access8484@aol.com
ICQ#: 51055819 (http://www.icq.com/51055819)


[This message has been edited by Serge (edited 11-19-1999).]

[beercat]

What if the table you're trying to drop doesn't exist at the time? If I'm using a temporary table to store data for reports, I want to check for its' existence and if found, then drop it, right? If it isn't found and you try to drop it, won't that return an error? Or should I just trap that error and Resume Next?

Thanks a lot for your help Serge. I really appreciate it.

Brian