Writing to a SQL database

Currently I have an project that writes data to an Access database. I would like to change this to write to a SQL database, but the documentation is less than stellar.

Could someone please point me in the right direction?Below is the code I'm using to connnect to the access database



Dim dbConn As New ADODB.Connection
    Dim dbRst As New ADODB.Recordset
    dbConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Data\Projects\DBMS\test.mdb"

 

If you use Wndows integrated security,



 



    dbConn.ConnectionString="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=DataBaseServerName"



 



if you use SQL Server user login,



 



    dbConn.ConnectionString="Provider=SQLOLEDB.1;Persist Security Info=False;Initial Catalog=DatabaseName;Data Source=DataBaseServerName;UID=UserLoginName;PWD=UserLoginPassword"



"kb" <-prairie.ab.ca> wrote in message news:...

Currently I have an project that writes data to an Access database. I would like to change this to write to a SQL database, but the documentation is less than stellar.

Could someone please point me in the right direction?Below is the code I'm using to connnect to the access database



Dim dbConn As New ADODB.Connection
    Dim dbRst As New ADODB.Recordset
    dbConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Data\Projects\DBMS\test.mdb"

 

I've read some things about setting this up, and all the documentation I have found says that I first must change
defaultType = dbUseODBC
Then setup a workspace. I've tried to do all of this with no luck. Is it required?

 

Simply try



 



On Error Resume Next



dbConn.ConnectionString="....."    'A valid ConnectionString to connect to SQL Server, one of the two from my previous reply.



dbConn.Open



If Err.Number<>0 Then



    'If you see this MessageBox pops up, the ConnectionString could be wrong



    'If you do not see this, the connection is opened



    MsgBox Err.Description   



    Exit Sub



End IF



 



'Open your RecordSet here.



dbRst.Open "MY_Grid", dbConn, adOpenKeyset, adLockOptimistic, adCmdTable



If Err.Number<>0 Then



    'Something wrong with RecordSet.Open(), maybe, wrong table name?



    MsgBox Err.Descritpion



End If



"kb" <-prairie.ab.ca> wrote in message news:...

I've tried the above supplied code for connecting to SQL (thanks) but I'm recieveing an error. Below is the error message:

The connection cannot be used to perform this operation. It is either closed or invalid in this context.



I thinks it's to do with the record set variables.

dbRst.Open "MY_Grid", dbConn, adOpenKeyset, adLockOptimistic, adCmdTable

 

Well, I got an error. "Invalid connection string attribute"

This is what I'm using:
dbConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=gis_parcel;DataSource=SQL2\INST2"

 

Compose a ConnectionString from nowhere is always a tough thing to do. Even with many years of DB programming, I often get a valid ConnectionString from my previous project.



 



I also often use data link file (*.udl file) to get me a valid ConnectionString to particular data source. To use data link file to lead you to a valid ConnectionString,



 



1. Use Notepad to save a blank text file with extension *.udl. For example, you save a blank file as "MyConnection.udl".



2. Double-click "MyConnection.udl", "Data Link Properties" dialog box will be brought up;



3. Make appropriate selections and click "Test Connection" button to verify that your selections are correct. Then close the dialog box.



4. Open "MyConnection.udl" file in Notepad, you can see a valid ConnectionString to particular data source. You can copy this ConnectionString to your project.



 



"kb" <-prairie.ab.ca> wrote in message news:...

Well, I got an error. "Invalid connection string attribute"

This is what I'm using:
dbConn.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=gis_parcel;DataSource=SQL2\INST2"

 

That worked!!! Very simple to change from Access to SQL with that. Thanks.