Page 1 of 1

Creating a Specific Table(If Not present already) In SQL with VB6 A nice shortcut when you want to run your database-Related application Rate Topic: ***** 1 Votes

#1 Nikhil_07n  Icon User is offline

  • The cheese stands alone..
  • member icon

Reputation: 49
  • View blog
  • Posts: 2,489
  • Joined: 09-January 09

Post icon  Posted 25 January 2009 - 02:20 AM

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

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.

2)Main Coding


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.

Is This A Good Question/Topic? 0
  • +

Replies To: Creating a Specific Table(If Not present already) In SQL with VB6

#2 sexy_me502  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 12
  • Joined: 30-September 09

Posted 30 September 2009 - 08:37 PM

> 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
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1