Open SQL database using VB

[JoShaw]

Help!! I am trying to import data to a SQL server database from a text file using VB 6.0.

When I try to open the SQL server using VB, I am getting error messages. Does anyone know the syntax or pathname for opening a SQL database, and then getting to objects within the database such as tables etc.

Thanks

Jo

 

[Vince]

Hi,

You could try using the catalog object

withing the VB project references select

Microsoft ADO Ext. 2.5 For DDL and Security

within your project

Create a module wide declaration

dim cn as adodb.connection


create a function called connect

Private function Connect() as boolean 

set cn = new connection 

cn.connectionString = "DSNNAME" 

cn.open 

if cn.state <> 1 then 
   'the database failed to connect
   connect = false 
else 
   'connection successful 
   connect = true 
end if 

end function

'to get a list of the tables and columns in the database
'Create a function called enumTables

Private Sub enumTables()
Dim tbl As Table
Dim oCol As Column
Dim oCat As ADOX.Catalog
Dim oNode As Node

'attept to create a connection
if connect = true then 

'set the connection information for the catalog
oCat.ActiveConnection = cn

'loop the catalog for each table
For Each tbl In oCat.Tables
    'add the table to the treeview
    Set oNode = TreeView1.Nodes.Add(, , , tbl.Name)
        'loop each column of that table and at it as a child of that table node
        For Each oCol In tbl.Columns
            TreeView1.Nodes.Add oNode, tvwChild, , oCol.Name
        Next oCol
      
Next tbl

'close
Set oCat = Nothing

end if 

End Sub

I hope that this helps

>:O>

 

[cdukes]

The Normal way to access SQLServer is via ADO (Microsoft Active Data Objects). In your VB project pass a reference to ADO.

Create a connection object and set up the Connection string:

Dim objadoConn as ADo.Connection

set objadoConn = new ADODB.Connection

objadoConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=false;Data Source=SERVERNAME;Initial Catalog=DATABASENAME"

objadoConn.open


Once you have opened a connection you can use the ADODB.Recordset and ADODB.Command objects to run stored procedures and queries on SQLServer.

For more info I suggest you look up MSDN or buy a book on ADO.

Hope this helps,

Chris Dukes