Invalid Bookmark error

[Duncan Handley]

Hi,

When trying to call an access query through vba in excel and copy the data
to the sheet using CopyFromRecordSet I get an error that says:

[Microsoft][ODBC Microsoft Access Driver]Invalid Bookmark

I'm pretty certain that this code did work fine, which is making me think
that its a data corruption (although I did repair the DB to no avail).

The code that creates this looks like:


Public Sub RefreshPostRebalance(targetSheet As Worksheet, topLeftCell As
Range, dateRebalance As Date)
On Error GoTo ErrorHandler
Dim conn As ADODB.Connection
Dim recSet As New ADODB.Recordset

Dim cmdString As String
Dim colOffset As Long
Dim colIndex As Long
Dim rowOffset As Long
Dim colCount As Long
Dim tradesRowStart As Long

' Clear down sheet
Range(topLeftCell, targetSheet.Cells(topLeftCell.Row + 300, 30)).Clear

' Open up DB connection
Set conn = New ADODB.Connection
connString = DBConnect.GetReadOnlyDBConnString()
conn.Open (connString)

' Set cursor to static so that we can see record counts
recSet.CursorType = adOpenStatic

rowOffset = topLeftCell.Row
colOffset = topLeftCell.Column


' *********************************
' * Place First Data - Works Fine*
' *********************************

cmdString = "EXEC GetProc1 #" & FormatDateTime(dateRebalance,
vbLongDate) & "#"
recSet.Open cmdString, conn

If (recSet.RecordCount > 0) Then
colCount = recSet.Fields.Count
AddTitleBar targetSheet, rowOffset, colOffset, colOffset + colCount
- 1, "NAV Used"

rowOffset = rowOffset + 2

' Place field titles
colIndex = colOffset
For Each fldItem In recSet.Fields
targetSheet.Cells(rowOffset, colIndex).Value = fldItem.Name
targetSheet.Cells(rowOffset, colIndex).Font.Bold = True
targetSheet.Cells(rowOffset,
colIndex).Borders(xlEdgeBottom).LineStyle = xlContinuous
targetSheet.Cells(rowOffset, colIndex).Interior.ColorIndex = 35
colIndex = colIndex + 1
Next

' Copy data
rowOffset = rowOffset + 1
targetSheet.Cells(rowOffset, colOffset).CopyFromRecordset recSet

rowOffset = rowOffset + recSet.RecordCount
End If

recSet.Close

' ******************************************
' * Place Second lost of Data - COPy breaks
' *****************************************

cmdString = "EXEC GetProc2 #" & FormatDateTime(dateRebalance,
vbLongDate) & "#"
recSet.Open cmdString, conn

If (recSet.RecordCount > 0) Then
If recSet.Fields.Count > colCount Then
colCount = recSet.Fields.Count
End If

rowOffset = rowOffset + 3
AddTitleBar targetSheet, rowOffset, colOffset, colOffset +
recSet.Fields.Count - 1, "Proposed Trades"
rowOffset = rowOffset + 2

' Place NAV field titles
Dim iTCurrentWeight As Long
Dim iTNewWeight As Long
Dim iTTargetWeight As Long

iTCurrentWeight = -1
iTNewWeight = -1
iTTargetWeight = -1

colIndex = colOffset

tradesRowStart = rowOffset

For Each fldItem In recSet.Fields

Select Case StrConv(fldItem.Name, vbUpperCase)
Case "EXISTINGWEIGHTING"
iTCurrentWeight = colIndex
Case "NEWWEIGHTING"
iTNewWeight = colIndex
Case "TARGETWEIGHTING"
iTTargetWeight = colIndex
End Select

targetSheet.Cells(rowOffset, colIndex).Value = fldItem.Name
targetSheet.Cells(rowOffset, colIndex).Font.Bold = True
targetSheet.Cells(rowOffset,
colIndex).Borders(xlEdgeBottom).LineStyle = xlContinuous
targetSheet.Cells(rowOffset, colIndex).Interior.ColorIndex = 35
colIndex = colIndex + 1
Next

' Copy data
rowOffset = rowOffset + 1

' ************
' BELOW COPY CAUSES Invalid Bookmark Error
' ************
targetSheet.Cells(rowOffset, colOffset).CopyFromRecordset recSet

End If

recSet.Close
conn.Close

Exit Sub

ErrorHandler:
MsgBox Err.Description
End Sub

 

[Tom Wickerath]

Hi Duncan,

I would consider re-posting this issue to the Excel Programming group, since
your code is in Excel:

http://www.microsoft.com/office/com...x?dg=microsoft.public.excel.programming&exp=1

It might also be helpful if you could provide sanitized versions (no
sensitive data) of your Excel and Access files, so that one could download
the files and have something to step through the code with. You'd have to be
able to post the files to some web site, perhaps a personal web site of your
own.


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

Hi,

When trying to call an access query through vba in excel and copy the data
to the sheet using CopyFromRecordSet I get an error that says:

[Microsoft][ODBC Microsoft Access Driver]Invalid Bookmark

I'm pretty certain that this code did work fine, which is making me think
that its a data corruption (although I did repair the DB to no avail).

The code that creates this looks like:


Public Sub RefreshPostRebalance(targetSheet As Worksheet, topLeftCell As
Range, dateRebalance As Date)
On Error GoTo ErrorHandler
Dim conn As ADODB.Connection
Dim recSet As New ADODB.Recordset

Dim cmdString As String
Dim colOffset As Long
Dim colIndex As Long
Dim rowOffset As Long
Dim colCount As Long
Dim tradesRowStart As Long

' Clear down sheet
Range(topLeftCell, targetSheet.Cells(topLeftCell.Row + 300, 30)).Clear

' Open up DB connection
Set conn = New ADODB.Connection
connString = DBConnect.GetReadOnlyDBConnString()
conn.Open (connString)

' Set cursor to static so that we can see record counts
recSet.CursorType = adOpenStatic

rowOffset = topLeftCell.Row
colOffset = topLeftCell.Column


' *********************************
' * Place First Data - Works Fine*
' *********************************

cmdString = "EXEC GetProc1 #" & FormatDateTime(dateRebalance,
vbLongDate) & "#"
recSet.Open cmdString, conn

If (recSet.RecordCount > 0) Then
colCount = recSet.Fields.Count
AddTitleBar targetSheet, rowOffset, colOffset, colOffset + colCount
- 1, "NAV Used"

rowOffset = rowOffset + 2

' Place field titles
colIndex = colOffset
For Each fldItem In recSet.Fields
targetSheet.Cells(rowOffset, colIndex).Value = fldItem.Name
targetSheet.Cells(rowOffset, colIndex).Font.Bold = True
targetSheet.Cells(rowOffset,
colIndex).Borders(xlEdgeBottom).LineStyle = xlContinuous
targetSheet.Cells(rowOffset, colIndex).Interior.ColorIndex = 35
colIndex = colIndex + 1
Next

' Copy data
rowOffset = rowOffset + 1
targetSheet.Cells(rowOffset, colOffset).CopyFromRecordset recSet

rowOffset = rowOffset + recSet.RecordCount
End If

recSet.Close

' ******************************************
' * Place Second lost of Data - COPy breaks
' *****************************************

cmdString = "EXEC GetProc2 #" & FormatDateTime(dateRebalance,
vbLongDate) & "#"
recSet.Open cmdString, conn

If (recSet.RecordCount > 0) Then
If recSet.Fields.Count > colCount Then
colCount = recSet.Fields.Count
End If

rowOffset = rowOffset + 3
AddTitleBar targetSheet, rowOffset, colOffset, colOffset +
recSet.Fields.Count - 1, "Proposed Trades"
rowOffset = rowOffset + 2

' Place NAV field titles
Dim iTCurrentWeight As Long
Dim iTNewWeight As Long
Dim iTTargetWeight As Long

iTCurrentWeight = -1
iTNewWeight = -1
iTTargetWeight = -1

colIndex = colOffset

tradesRowStart = rowOffset

For Each fldItem In recSet.Fields

Select Case StrConv(fldItem.Name, vbUpperCase)
Case "EXISTINGWEIGHTING"
iTCurrentWeight = colIndex
Case "NEWWEIGHTING"
iTNewWeight = colIndex
Case "TARGETWEIGHTING"
iTTargetWeight = colIndex
End Select

targetSheet.Cells(rowOffset, colIndex).Value = fldItem.Name
targetSheet.Cells(rowOffset, colIndex).Font.Bold = True
targetSheet.Cells(rowOffset,
colIndex).Borders(xlEdgeBottom).LineStyle = xlContinuous
targetSheet.Cells(rowOffset, colIndex).Interior.ColorIndex = 35
colIndex = colIndex + 1
Next

' Copy data
rowOffset = rowOffset + 1

' ************
' BELOW COPY CAUSES Invalid Bookmark Error
' ************
targetSheet.Cells(rowOffset, colOffset).CopyFromRecordset recSet

End If

recSet.Close
conn.Close

Exit Sub

ErrorHandler:
MsgBox Err.Description
End Sub