how to create a table in ms access db using vb code?

Hi,

Could someone please give me an idea how to create a new table in an Access 2002 database, I have a Message Table containing information like user, message, reply to, date. This is an online chat conversation.

I want VB to create a new table in the database by looking at the current data in the database.

My main problem is that I need a new table created which has three columns :

User and Question Mark and Message.. the table needs to be populated in the following way :

I want it to look at the message in the Message Table and if it contains a question mark put YES in the question mark field and have the associated username.

I havent really used VB before so dont really know how to go about doing this.
I would really appreciate your help
-x-

Open Access. Press Alt-F11. Press F1. Search help for "create table". Look at the Create Table statement documentation for details.

The following code will create a table in an access database.

Private Sub Form_Load()
Const strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\n13437\My Documents\db1.mdb;Persist Security Info=False"

Dim cnn As New ADODB.connection
Dim cmd As New ADODB.Command

cnn.ConnectionString = strConnection
cnn.Open

cmd.ActiveConnection = cnn
cmd.CommandText = "create table tnn1 (a varchar(10), b number)"

cmd.Execute
cnn.Close

End Sub

Note that you will have to change the database name and path in the connection string to match
your situation. Also you will have to change the command text to create the fields and types that you need. You will also have to add a reference to Microsoft Active X data objects in the references dialog.
Select Project | References from the menu.

Great thanks for the help, I'll try it out

-x-

Hey that worked thanks alot..

Adding to this would anyone be able to advise how to populate the new table created by vb ..

I need vb to create a few tables.. one is to extract the following fields from the existing table : User, Message but with the additional field Question Mark ..

I either just need it to extract all the messages that contain a question mark or put a yes or no in the question mark field along side the particular message..

The other one I need.. is for a new table to be created by VB and for it to count from the existing table how many users send a message, so the new VB table needs to be populated with user and then the number of messages sent.

I have a query that does that.. can that be connected to the vb ? .. once the vb table has that data I need it to extract the person who sent the maximam messages.

The query is :

SELECT User, count(*) AS [No of messages]
FROM [Message Table]
GROUP BY User;

Can anyone advise on any of the above?

Many thanks
-x-

You can also use the ADO Command object to issue various SQL statements including INSERT, APPEND....

Look at the following code: Note that this is almost the same code you used to create the table with three changes.
I added a statement Dim rs as New ADODB.Recordset. This creates a recordset object
Next I modified the cmd.execute statement as fillows:
Set rs = cmd.execute This places the resulting rowset or recordset created when the command object executes the SQL into the
recordset rs. Finally I changed the command text to a SELECT * FROM....statement.
You can put just about any SQL statements in the command text. In this case, I coded a loop to print the contents of the first three fields of the recordset to the immediate window of the debugger. Don't forget the rs.movenext statement or you will have an infinite loop.

Private Sub Form_Load()
Const strConnection As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\user\My Documents\db1.mdb;Persist Security Info=False"
Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset 'New from previous code.
cnn.ConnectionString = strConnection
cnn.Open
cmd.ActiveConnection = cnn
cmd.CommandText = "SELECT * FROM CHURCH100" 'changed command text
Set rs = cmd.Execute 'changed from prrevious code.

'I added this loop so you can see the results of our work...
Do Until rs.EOF
Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2)
rs.MoveNext
Loop

cnn.Close
End Sub

i should state that i use MS Access 2003.......the post i used as a guidline was for '02...not sure if there is much of a difference......

t