Table Creation in VB6.0 ADO

Posted on 1999-07-12
Medium Priority
Last Modified: 2013-12-25
How do you CREATE a table using VB6.0 and ADO?  How do create and add the fields?
Question by:Calibus
  • 2

Author Comment

ID: 1506487
Edited text of question.

Expert Comment

ID: 1506488
You can use MS ADO Ext 2.1 for DDL & Security for creating/modifiying table. There are Tables & Columns collections which can be used to add a new table/column.

Hope this helps

Author Comment

ID: 1506489
I'd like to know HOW, as in a ADO code sample.

Accepted Solution

CraigLazar earned 150 total points
ID: 1506490
Hi there this is what i use in my apps
Dim DB As New ADODB.Connection
Dim Rs As New ADODB.Recordset

    DB.Open "Provider=SQLOLEDB.1;Persist Security Info=False;" _
    & "User ID=" & tmpLogonName & ";Password=" & tmpPassword & ";" _
    & "Initial Catalog=Customers;Data Source=Devserv"
    Rs.Open SqlAdo, DB, adOpenStatic, adLockReadOnly, adCmdText

DB.Execute "CREATE TABLE MyTable (FirstName TEXT, LastName TEXT);"

Creates a new table.

Note   The Microsoft Jet database engine doesn't support the use of CREATE TABLE, or any of the DDL statements, with non-Microsoft Jet database engine databases. Use the DAO Create methods instead.


CREATE TABLE table (field1 type [(size)] [NOT NULL] [index1] [, field2 type [(size)] [NOT NULL] [index2] [, ...]] [, CONSTRAINT multifieldindex [, ...]])

The CREATE TABLE statement has these parts:

Part      Description
table      The name of the table to be created.
field1, field2      The name of field or fields to be created in the new table. You must create at least one field.
type      The data type of field in the new table.
size      The field size in characters (Text and Binary fields only).
index1, index2      A CONSTRAINT clause defining a single-field index. See the CONSTRAINT clause topic for more information on how to create this index.
multifieldindex      A CONSTRAINT clause defining a multiple-field index. See the CONSTRAINT clause topic for more information on how to create this index.

Use the CREATE TABLE statement to define a new table and its fields and field constraints. If NOT NULL is specified for a field, then new records are required to have valid data in that field.
A CONSTRAINT clause establishes various restrictions on a field, and can be used to establish the primary key. You can also use the CREATE INDEX statement to create a primary key or additional indexes on existing tables.
You can use NOT NULL on a single field, or within a named CONSTRAINT clause that applies to either a single field or to a multiple-field named CONSTRAINT. However, you can apply the NOT NULL restriction only once to a field, or a run-time error occurs.

This works well for me :0)
give it a try



Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

627 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question