Creating a table programatically

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Thatch - 24 Jul 2006 20:34 GMT

Does anyone have code that creates a table using ADO? I'm just looking to
create a table with int32, datatime and index. Any code on this.
Also, is there any code that will query a database whether a specific table
exists or not?

I want to query a database if a particular table exists or not and if not,
to be able to create it. Unfortunately, I haven't found ANYTHING on the net
that does that.

Thanx in advance.

Reply to this Message

Ralph - 24 Jul 2006 23:54 GMT

> Does anyone have code that creates a table using ADO? I'm just looking to
> create a table with int32, datatime and index. Any code on this.
[quoted text clipped - 6 lines]
>
> Thanx in advance.

Create a reference to the ADOX library, "Microsoft ADO Ext 2.5 for DDL and
Security" and start playing. Just goggle the inet for usage examples.

Reply to this Message

Richard Mueller - 25 Jul 2006 23:53 GMT

>> Does anyone have code that creates a table using ADO? I'm just looking to
>> create a table with int32, datatime and index. Any code on this.
[quoted text clipped - 12 lines]
> Create a reference to the ADOX library, "Microsoft ADO Ext 2.5 for DDL and
> Security" and start playing. Just goggle the inet for usage examples.

Hi,

In VB I add a reference to "Micrsoft ActiveX Data Objects 2.8 library",
which is msado15.dll. The version doesn't matter much. The trick is finding
a connection string for you DBMS. You may have to search on the Internet for
examples for you DBMS. Below is for SQL Server. In the example below I test
for the existence of the table MyTable. If it exists, I delete it. Then I
create it. The T-SQL statements are passed as strings to the ADO command
object. Another trick is taking T-SQL statements and enclosing them in
quotes, getting all the spaces, commas, etc. correct. An example:
===================
strDB = "MyDatabase"
strServer = "MyServer"
strInstance = "MyInstance"

' Construct connection string for SQL Server.
' If there is no instance, omit "\" and strInstance.
' This assumes integrated Windows authentication.
strDBConnect = "DRIVER=SQL Server;" _
& "Trusted_Connection=Yes;" _
& "DATABASE=" & strDB & ";" _
& "SERVER=" & strServer & "\" & strInstance

' Connect to SQL Server with ADO Connection object.
Set adoConnection = New ADODB.Connection
adoConnection.ConnectionString = strDBConnect
adoConnection.Open

' Setup ADO command object.
Set adoCommand = New ADODB.Command
adoCommand.ActiveConnection = adoConnection
adoCommand.CommandType = adCmdText

' If table MyTable exists, delete it.
strSQL = "IF EXISTS " _
& "(" _
& "SELECT TABLE_NAME " _
& "FROM INFORMATION_SCHEMA.TABLES " _
& "WHERE TABLE_NAME = 'MyTable'" _
& ") " _
& "DROP TABLE MyTable"

adoCommand.CommandText = strSQL
adoCommand.Execute

' Create table MyTable.
strSQL = "CREATE TABLE MyTable " _
& "(" _
& "Field1Name INTEGER NOT NULL, " _
& "Field2Name VARCHAR(24) NULL, " _
& "Field3Name DATETIME NULL, " _
& "Field4Name MONEY NULL, " _
& "Field5Name BIT NOT NULL, " _
& "Field6Name VARCHAR(50) NULL, " _
& ")"

adoCommand.CommandText = strSQL
adoCommand.Execute

Signature

Richard
Microsoft MVP Scripting and ADSI
Hilltop Lab - http://www.rlmueller.net

Reply to this Message

Veign - 26 Jul 2006 22:35 GMT

Many samples at:
http://www.veign.com/vrc_app_cat.asp?catid=9

Signature

Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
http://www.veign.com/vrc_main.asp
Veign's Blog
http://www.veign.com/blog
--

> Does anyone have code that creates a table using ADO? I'm just looking to
> create a table with int32, datatime and index. Any code on this.
[quoted text clipped - 8 lines]
>
> Thanx in advance.

Reply to this Message

VB Forum / Database Access / July 2006

Creating a table programatically