ADO/SQL and the AddNew statement.

[Steve Thomas]

This is code that I used with DAO and Access97. However, when I try and run it with ADO and SQL server I get an error on the RS.AddNew "Object or Provider is not capable of performing this type of operation." I have looked at doing this through Insert statements, Bulk Copy, or DTS but I am perfornming alot of if then statements that seem to be easier with the Line Input function rather than copying to one table then manipulating, then copying to another table, then deleteing temp table etc, etc, etc. I have been working with SQL server for about 2 weeks now and I can't say that I like it. (I hope it gets better).


'************************************************
rs.CursorLocation = adUseClient
rs.Open "Select * From tMaster", cn, adOpenDynamic

Open (App.Path & "\import\paid.txt") For Input As 1
While Not EOF(1)
Line Input #1, sRecord
rs.AddNew

rs!acctNum = "1234567"

rs.Update
Wend
'***************************************************

[Jaguar]

What do your connection property settings look like? Which Provider are you trying to use? etc. give more code from before this set of code.

[Steve Thomas]

I found an article that suggests I try the following connection string but I can't seem to get it to work.

rs.OPEN("select * from authors", ;
"DRIVER={SQL Server};"+;
"SERVER=YourServerName;"+;
"DATABASE=pubs;"+;
"UID=YourUserName;"+;
"PWD=YourPassword",;
adOpenDynamic, adLockOptimistic)


Here is the full code:

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
cn.Open "provider=SQLOLEDB.1;" & _
"Password=;" & _
"Persist Security Info=False;" & _
"User Id=sa;" & _
"Initial Catalog=TPO;" & _
"Data Source=SLC-CUSTSQL"

rs.CursorLocation = adUseServer
rs.Open "Select Top 1 * From tMaster", cn

Open (App.Path & "\import\paid.txt") For Input As 1
While Not EOF(1)
Line Input #1, sRecord
rs.AddNew
rs.acctnum , '1234567'
rs.Update
Wend
Close
rs.Close

[Clunietp]

I don't think SQL supports a dynamic cursor...try using a keyset cursor

or try a connection string like this:

rs.Open "Select * from authors", _
"Provider=SQLOLEDB;Data Source=SERVERNAME;UID=USERNAME;PWD=PASSWORD;" & _
"Initial Catalog=DatabaseName", adOpenKeyset, adLockOptimistic

here's an example of adding a new record to the pubs database on a SQL server:
1. start a new standard exe
2. set a reference to whatever version of ado is on your machine
3. add a command button to the default form
4. cut and paste the following code to the form module:

Option Explicit

Dim strConnectionString As String
Dim strSqlCmd As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Public Sub Test()
      
      'change the name of the SERVER to the SQL server you are using
      strConnectionString = "PROVIDER=SQLOLEDB;USER ID=sa;PASSWORD=;" & _
                                    "INITIAL CATALOG=pubs;SERVER=rdev1;"
      
      ' Set up the connection
      Set cn = New ADODB.Connection
      cn.ConnectionString = strConnectionString
      cn.Open
      
      'set up the recordset
      strSqlCmd = "select * from authors"
      Set rs = New ADODB.Recordset
      rs.Source = strSqlCmd
      rs.ActiveConnection = cn
      
      
      rs.Open , , adOpenDynamic, adLockOptimistic
      If Not (rs.BOF And rs.EOF) Then
            Do While Not rs.EOF
                  Debug.Print rs("au_id") & " --> " & rs("au_lname") & ", " & rs("au_fname")
                  rs.MoveNext
            Loop
' ------------------------------------------------------------------------------------
'     Uncomment the following when you want to add a new record 
'     to the authors table in the pubs database
' ------------------------------------------------------------------------------------
'            rs.AddNew
'            rs("au_id") = "555-12-1212"
'            rs("au_lname") = "MyLastName"
'            rs("au_fname") = "MyFirstName"
'            rs("phone") = "925-123-4567"
'            rs("address") = "my address"
'            rs("city") = "my city"
'            rs("state") = "CA"
'            rs("zip") = "12345"
'            rs("contract") = 0
'            rs.Update
' -------------------------------------------------------------------------------------
            rs.Close
      End If
      ' Clean Up
      If Not rs Is Nothing Then Set rs = Nothing
      If Not cn Is Nothing Then Set cn = Nothing
End Sub

Private Sub Command1_Click()
      Call Test
End Sub

...when you get the hang of SQL Server you'll never want to use MSAccess again.