1) Open connection.
Set adoconn = New ADODB.Connection adoconn.Open "Provider=MSDAORA;user id=scott;password=tiger;" adoconn.CursorLocation = adUseClient
Explanation : Not needed , I think.
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
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.