CODEWITHSUNDEEP
ms-accessvba
Hello World
Hello World

Reputation: 308

MS Access VBA working with AbsolutePosition

I have a situation where I need to run a pass through append/insert query of values from MS-Access 2010 VBA to SQL Server 2012, which I have working fine, but that may at some future point need to work with over 1000 records, so I need to make sure my code will accommodate that. My idea was to use modulo to determine when I hit the 1000th record to add a new ; insert ...., to the pass through string, not sure if it's the best approach anyway but I'm struggling to work with .AbsolutePosition.

So far I have:

    Dim rs As DAO.Recordset
    Dim strsql As String
    Set rs = CurrentDb.OpenRecordset("MyTable")
    strsql = "insert into [dbo].[SomeTable](field1, field2) values"
    If Not (rs.EOF And rs.BOF) Then
        rs.MoveFirst 
        Do Until rs.EOF = True
            msgbox rs.AbsolutePosition 'error with whatever I do here
    'Something like the below is what I actually want to get to
    'If rs.AbsolutePosition Mod 999 = 0 Then strsql = Left(strsql, Len(strsql) - 1) & "; insert into [dbo].[SomeTable](field1, field2) values"
            strsql = strsql & " (" & rs!field1 & "," & rs!field2 & "'),"
            rs.MoveNext
        Loop
        strsql = Left(strsql, Len(strsql) - 1) & ";"
    Else
        MsgBox "No Data"
        Exit Sub
    End If

I keep getting an error operation is not supported for this type of object when either trying to return or perform a calculation using rs.AbsolutePosition

Upvotes: 1

Views: 2029

Answers (1)

Andre
Andre

Reputation: 27644

From the documentation:

The AbsolutePosition property isn't available on forward–only–type Recordset objects, or on Recordset objects opened from pass-through queries against Microsoft Access database engine-connected ODBC databases.

Why not simply use a running counter?

Do Until rs.EOF = True
    i = i + 1
    If i Mod 1000 = 0 Then
        ' ...

Edit

/Fail. Your rs isn't from a Pass-Through query, so that's not the issue. Nevertheless, the running counter seems like the easiest option.

Upvotes: 1

Related Questions