Creating a Specific Table(If Not present already) In SQL with VB6, A nice shortcut when you want to run your database-Related application Options Rating

[Nikhil_07n]

Introduction : This program uses adodb control, 'On error' checks to create a table when the code is runned.

1) Open connection.

CODE

Set adoconn = New ADODB.Connection
adoconn.Open "Provider=MSDAORA;user id=scott;password=tiger;"
adoconn.CursorLocation = adUseClient

Explanation : Not needed , I think.

2)Main Coding

CODE

Set cmdcrtTable = New ADODB.Command
Set reccrtTable = New ADODB.Recordset

On Error GoTo err1
reccrtTable.Open "email", adoconn, adOpenDynamic, adLockOptimistic, adCmdTable

err1:
cmdcrtTable.CommandText = "Create table Email(fname varchar2(20),lname varchar2(20),uname varchar2(10),pass varchar2(10))"
cmdcrtTable.ActiveConnection = adoconn
On Error GoTo err2
cmdcrtTable.Execute

err2:

'absolutely nothing to do

Explanation :

Lets learn this by taking two cases :

1)The Table 'email' already exists

When the recordset 'reccrtTable' is tried to be opened, it opens without any error and normal executing is done.
the program then , sequentially gets executed, and tries to execute the command 'cmdcrtTable'.........BUT.........as the table already exists, the execution returns an error and the program JUMPS to 'err2' and , eventually, nothing is done and ONLY a recordset is opened.

Result : Without any error reccrtTable gets opened

2)The table 'email' does not exist

When the recordset 'reccrtTable' is tried to be opened, the line returns an ERROR, so the program jumps to 'err1' and without any error the 'cmdcrtTable' gets executed and the table is created.

Result : Without any error cmdcrtTable gets executed and the table 'email' is created.

===============================================

This code is one of my fav.s as its very logical.

I created this after thinking a lot, as i dint knew about any functions for checking whether a table exists or not.

Its specially useful when you want to run your database-linked program on a diff. computer.

[sexy_me502]

> i tried your codes but it doesnt seem to work, maybe i've done something wrong. error message says...

"user-defined type not define"

the error pointing at

Set adoconn = New ADODB.Connection