shayne23d
asked on
accessing sql from vb code
I am trying to access tables in a sql database using vb6 and adodb and recordset
I have only ever done this from vb to access. I am not using any forms
to make it simple lets call the data base test and the table person. in access I think I did
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim com As ADODB.Command
'create the connection to the database.
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OL
cn.Open
' Get the record set.
Set rs = New ADODB.Recordset
rs.open "Select * from person"
Ok second half of hte question is how to I update the database with code.
Thanks in advance, loss newbie programmer
I have only ever done this from vb to access. I am not using any forms
to make it simple lets call the data base test and the table person. in access I think I did
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim com As ADODB.Command
'create the connection to the database.
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OL
cn.Open
' Get the record set.
Set rs = New ADODB.Recordset
rs.open "Select * from person"
Ok second half of hte question is how to I update the database with code.
Thanks in advance, loss newbie programmer
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
you can look at www.connectionstrings.com for loads of connection strings. alternatively you could set up a DSN in the ODBC and use that as your connection string: "DSN=your_dsn_name;USER=us
to update the database simply create your SQL string and use the database connection to execute the query.
strSql = "UPDATE persons SET Age=100 WHERE name='Bill'"
cn.Execute(strSql)
Hope this helps,
Steve
to update the database simply create your SQL string and use the database connection to execute the query.
strSql = "UPDATE persons SET Age=100 WHERE name='Bill'"
cn.Execute(strSql)
Hope this helps,
Steve
You can use properties of Connection object instead of connection strings, it is more simple
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim com As ADODB.Command
'create the connection to the database.
Set cn = New ADODB.Connection
With cn
.Provider = sProvider
.CursorLocation = adUseClient
.Properties("Data source") = DatabaseServerName
.Properties("User ID") = DatabaseUserName
.Properties("Password") = DatabasePassword
.Properties("Initial Catalog") = Database Name
.Open
end with
'Then use the recordsets same as that of access.
' Get the record set.
Set rs = New ADODB.Recordset
rs.open "Select * from person"
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Dim com As ADODB.Command
'create the connection to the database.
Set cn = New ADODB.Connection
With cn
.Provider = sProvider
.CursorLocation = adUseClient
.Properties("Data source") = DatabaseServerName
.Properties("User ID") = DatabaseUserName
.Properties("Password") = DatabasePassword
.Properties("Initial Catalog") = Database Name
.Open
end with
'Then use the recordsets same as that of access.
' Get the record set.
Set rs = New ADODB.Recordset
rs.open "Select * from person"
check out:
http://www.able-consulting.com/MDAC/ADO/Connection/OLEDB_Providers.htm
for the SQL Server connection strings.
AW