Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2012
    Posts
    3

    Exclamation Unanswered: Connecting to a SQL Server table to add records

    I am trying to push data to a number of SQL server tables.

    I have tried ADO (vba that works perfectly in excel) but I can seem to open the recordset in Access.

    Dim strSQL As String
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim ServerName, DatabaseName As String

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    rs.CursorLocation = adUseClient

    ServerName = "MPRMS"
    DatabaseName = "RMS_Price"
    cn.Properties("Data Source").Value = ServerName
    cn.Properties("Initial Catalog").Value = DatabaseName
    cn.Open

    strSQL = "SELECT * FROM p_BlockPositions"

    rs.Open strSQL, cn, adOpenKeyset, adLockOptimistic

    For I = 1 To iRecordCount
    With rs
    .AddNew
    .Fields("BlockPosition_id").Value = I
    .Fields("TradeDate").Value = Format(Source(I).TradeDate, "mm/dd/yyyy")
    .Fields("BuySell").Value = Source(I).BuySell
    .Fields("DelMo").Value = Format(Source(I).DelMo, "mm/dd/yyyy")
    .Fields("DealNumber").Value = Source(I).PSE_DealNumber + 1
    .Fields("Curve").Value = Source(I).Curve
    .Fields("Price").Value = Source(I).Price
    .Fields("CounterParty").Value = Source(I).CounterParty
    .Fields("Description").Value = Source(I).Description
    .Fields("Broker").Value = Source(I).Broker
    .Fields("HeatRate").Value = Source(I).HeatRate
    .Fields("MWH").Value = Source(I).MWH
    .Update
    End With
    Next I

    .... etc.

    Obviously ADO is problematic as I've tried a few variations (always errors at "rs.open..."). I thought DAO or some other method might allow me to accomplish this.

    Thanks.

    troy

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is source(i)?
    what error message are you getting
    ..and on whatline is the error reported

    if you are pushing data into another db, then you'd almost certainly be better off using an "insert into" rather than iterating through a recordset

    my guess is the problem is the array source()....
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2012
    Posts
    3

    Source(i)

    Source(I) is an array of defined types that i have loaded with the table data during previous processing from source data. It will perform essentially the same as a recordset.

    My issue is with the recordset connecting to the SQL server table that will enable me to employ the rs.addnew method.

    Thanks.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what error message are you getting?

    FWIW I would have expected there to be some code/instructionthat tells the ADO object that its trying to connect to SQL server. whether thats as a discrete property (Provider) or an all in once connection string isq up to you. no doubt the discrete property is the more modern approach.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2012
    Posts
    3

    Error Message

    "Run-time error '3709';

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

    I've tried with the connection string, and by setting the Data Source and Initial Catalog properties. Both yield the same results. The ServerName is defined in my system ODBC Data Sources... so it is essentially an alias.

    Thanks.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so what did you try as the connection string?

    I still don't see where you tell the ADO object you are using SQL server

    whether its an alias or not I's be very suspicious of making assumption about ODBC in relation to ADO they are not the same.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •