ODBC-MS Access Connection Call Failed Error 3146

[Irwan]

hello VB expert,

i am new in VB and need an advice.

i am trying to get actual value from Long Binary Data.
The Long Binary Data is stored in software database (the database is in network system) which i export using ODBC to MS Access.
After export to MS Access, i need to translate the Long Binary Data into actual value using VB.
Here is the code provided by the vendors, but it keeps give me error.

really appreciate if anyone can show how to solve this error.

Thank you.

 

[arnelgp]

What does the Long Binary Data holds, image, pdf, what?

 

[Irwan]

hello arnelgp,

it stored waveform/spectral data like time/frequency and its amplitude.

The goal is to extract the highlight yellow.

Really appreaciate for the help.
Thank you.

 

[arnelgp]

how many records does rs holds when you open the recordset? only 1?
after this line:

Set rs = db.OpenRecordset("Select RawData, LineResolution From VibSpectra Where SpectraKey = ...)

insert this line to save the Rawdata to a file (RawData.bin)

chunk = rs("RawData")
' arnelgp
Dim fileNum%, filePath$
filePath = Environ("Userprofile") & "\Documents\RawData.bin"
fileNum = FreeFile
Open filePath For Binary Access Write As fileNum
Put fileNum, , chunk
Close fileNum
Exit Sub
'end arnelgp

now you can post the RawData.bin (saved in your Documents)
so we can examine the structure (or if you know the Structure, tell us what it is).

 

[sonic8]

I have some doubts whether OpenDatabase works with an ODBC connection string.
If it ever worked, it probably was part of the ODBC Direct functionality, which was removed with the switch from JET to ACE in A2007.

You can check the DBEngine.Errors collection for a more meaningful error message.

If OpenDatabase is the problem here, you could use a pass-through query as an alternative to query the data.

 

[arnelgp]

or you can try ADO.Connection/Recordset

 

[amorosik]

"..Here is the code provided by the vendors, but it keeps give me error.."

And the vendors, what says?

 

[RonPaii]

hello VB expert,

i am new in VB and need an advice.

i am trying to get actual value from Long Binary Data.
The Long Binary Data is stored in software database (the database is in network system) which i export using ODBC to MS Access.
After export to MS Access, i need to translate the Long Binary Data into actual value using VB.
Here is the code provided by the vendors, but it keeps give me error.
View attachment 121931
View attachment 121933
really appreciate if anyone can show how to solve this error.

Thank you.

3146 is the VBA error code for an ODBC error. These are ODBC error codes and 2 links for code to decipher and report them.

' DBEngine.Errors(0) errors
' http://accessmvp.com/TomVanStiphout/OdbcErrors.htm
' https://stackoverflow.com/questions/730414/determine-real-cause-of-odbc-failure-error-3146-with-ms-access
Public Enum ODBCErrorCodesEnum
    NoODBCError = 0
    ODBCCallFailed = 3146         ' From VBA/Access
    ConnectionFailed = 3151
    CouldNotFindDll = 3154
    InsertOnLinkedTableFailed = 3155
    DeleteOnLinkedTableFailed = 3156
    UpdateOnLinkedTableFailed = 3157
    FieldWouldBeToLongDataTruncated = 3231
    RemoteQueryTimeOut = 3234
    DataTypeNotSupportOnServer = 3235
    DataOutOfRange = 3238
    LinkedTableDefinitionChanged = 3247
    CannotLockReocords = 3254
    DuplicateValue = 2627           ' From Observation of log
End Enum

 

[Irwan]

Hello @arnelgp ,
do you mind sharing how to save rawdata.bin? i can share the data to this forum because i am not sure how the structure.
Also do you have an example VB code on how to do with ADO.Connection/Recordset? i am really newbie in VB.
Or if you have the similar VB code that pulling data from ODBC will be great.

Hello @sonic8 ,
you might be right, if i change OpenDatabase into wrk.OpenDatabase, the error is different.
Do you have another example VB code on how to pull data from ODBC connection using another similar code OpenDatabase?

Hello @amorosik ,
the vendor only gave the example as they only support the data related itself. They dont support helping making the code.
So i am really need to have many example on using how to get data from ODBC.

Hello @RonPaii ,
you might be right. Do you have an example VB code on getting data from ODBC?

Also i attached the file provided by vendor if the source is needed.

Really appreciate for all you help here.
Thank you.

 

[RonPaii]

Hello @arnelgp ,
do you mind sharing how to save rawdata.bin? i can share the data to this forum because i am not sure how the structure.
Also do you have an example VB code on how to do with ADO.Connection/Recordset? i am really newbie in VB.
Or if you have the similar VB code that pulling data from ODBC will be great.

Hello @sonic8 ,
you might be right, if i change OpenDatabase into wrk.OpenDatabase, the error is different.
Do you have another example VB code on how to pull data from ODBC connection using another similar code OpenDatabase?

Hello @amorosik ,
the vendor only gave the example as they only support the data related itself. They dont support helping making the code.
So i am really need to have many example on using how to get data from ODBC.

Hello @RonPaii ,
you might be right. Do you have an example VB code on getting data from ODBC?

Also i attached the file provided by vendor if the source is needed.

Really appreciate for all you help here.
Thank you.

Add error trapping to your existing code.

Private Sub Form_Load()
  On error goto errHandler

' Your Code that may error

Done:
  exit sub
errHandler:
  Debug.print "(" & err.Number & ") " & err.Description
  If err.Number = 3146 then
    ' ODBC error
    Dim errorItem As DAO.Error
    For Each errorItem In DBEngine.Errors
      Debug.Print errorItem.Source & "(" & errorItem.Number & ") " & errorItem.Description
    Next
  End If
  Resume Done
End Sub

 

[arnelgp]

Here is what ChatGPT has to say"

Private Sub Form_Load()
   
    Const conChunkSize = 4
    
    Dim lngOffset As Long
    Dim lngTotalSize As Long
    Dim chunk() As Byte
    Dim i As Long
    
    Dim rawData As Variant
    Dim data As Variant
    Dim rslt As String, j As Long
    
    ReDim chunk(conChunkSize)
    
    rawData = Null
    rawData = GetVibSpectraData( _
                                    "\\DomainHere\RBMNet\RBMSuite\CustData\Concentrating_CuCl_Plant_2024.rbm", _
                                    "Concentrating_CuCl_Plant_2024", _
                                    "SpectraKey = -3595158")
    
    If Not IsNull(rawData) Then
        lngTotalSize = LenB(rawData)
        i = 1: j = 1
        Do While lngOffset < lngTotalSize
            chunk = GetChunkBinary4(rawData, i, lngOffset)
            S.S = chunk
            LSet R = S
            rslt = rslt & j & vbTab & Format(R.R, "0.00000000") & vbCr & vbLf
            i = i + 4
            j = j + 1
        Loop
        'Debug.Print rslt
    End If
    Text1 = rslt

End Sub
        
Public Function GetVibSpectraData(ByVal AMSdbPath As String, ByVal dbName As String, ByVal WhereCondition As String) As Variant
    'Dim conn As ADODB.Connection
    'Dim rs As ADODB.Recordset
    Dim conn As Object
    Dim rs As Object
    Dim strConn As String
    Dim strSQL As String
    
    GetVibSpectraData = Null
    
    ' Initialize ADO connection
    'Set conn = New ADODB.Connection
    Set conn = CreateObject("ADODB.Connection")
    
    ' --- Modify these connection details for your AMS environment ---
    strConn = "Provider=SQLOLEDB;" & _
              "Data Source=" & AMSdbPath & ";" & _
              "Initial Catalog=" & dbName & ";" & _
              "Integrated Security=SSPI;"  ' or use: "User ID=xxx;Password=yyy;"
    
    ' Open the connection
    conn.Open strConn
    
    ' SQL query to fetch RawData and LineResolution for the given SpectraKey
    strSQL = "SELECT RawData, LineResolution " & _
             "FROM VibSpectra " & _
             "WHERE " & WhereCondition & ";"
    
    ' Open recordset
    'Set rs = New ADODB.Recordset
    Set rs = CreateObject("ADODB.Recordset")
    rs.Open strSQL, conn, 0, 1, 1       'adOpenForwardOnly, adLockReadOnly, adCmdText
    
    ' Check if any records were returned
    If Not (rs.EOF And rs.BOF) Then
        'Debug.Print "LineResolution: "; rs!LineResolution
        
        ' RawData might be binary — handle accordingly
        If Not IsNull(rs.Fields(0)) Then
            'Debug.Print "RawData length (bytes): " & LenB(rs!RAwData)
            GetVibSpectraData = rs.Fields(0)
        Else
            'Debug.Print "RawData is NULL"
        End If
    Else
        'MsgBox "No record found for SpectraKey = -3595158", vbExclamation
    End If
    
    ' Clean up
    rs.Close
    conn.Close
    Set rs = Nothing
    Set conn = Nothing
    
    'MsgBox "Query completed successfully.", vbInformation
End Function

Function GetChunkBinary4(ByVal blobData As Variant, ByVal offset As Long, _
                         ByRef nextOffset As Long) As Variant
    Dim data() As Byte
    Dim chunk() As Byte
    Dim i As Long, dataLen As Long, chunkSize As Long
    
    ' Validate input
    If IsNull(blobData) Then Exit Function
    If VarType(blobData) <> (vbArray + vbByte) Then Exit Function
    
    data = blobData
    dataLen = UBound(data) - LBound(data) + 1
    chunkSize = 4  ' fixed 4 bytes per read
    
    ' If offset exceeds data length, return nothing
    If offset > dataLen Then Exit Function
    
    ' Adjust chunk size if near the end
    If offset + chunkSize - 1 > dataLen Then
        chunkSize = dataLen - offset + 1
    End If
    
    ' Copy chunk
    ReDim chunk(0 To chunkSize - 1)
    For i = 0 To chunkSize - 1
        chunk(i) = data(LBound(data) + offset - 1 + i)
    Next i
    
    ' Calculate next offset
    nextOffset = offset + chunkSize
    
    ' Return chunk as byte array
    GetChunkBinary4 = chunk
End Function

 

[amorosik]

Hello @amorosik ,
the vendor only gave the example as they only support the data related itself. They dont support helping making the code.
So i am really need to have many example on using how to get data from ODBC.

I don't understand
The vendor gives you an example that doesn't work?

 

[sonic8]

Do you have another example VB code on how to pull data from ODBC connection using another similar code OpenDatabase?

I suggest you first start by creating a pass-through query manually with your connection string and a super simple query like "SELECT * FROM ATable".
If this works, you can try to replicate the manual approach with VBA and then build the query you actually want to use.

 

[Irwan]

hello @arnelgp ,
thank you for the code, gonna try this.

hello @sonic8 ,
yes, it probably might be better to check it from simple query

Thank you for your time looking this matter.

 

[BlueSpruce]

hello VB expert,

i am new in VB and need an advice.

i am trying to get actual value from Long Binary Data.
The Long Binary Data is stored in software database (the database is in network system) which i export using ODBC to MS Access.
After export to MS Access, i need to translate the Long Binary Data into actual value using VB.
Here is the code provided by the vendors, but it keeps give me error.
View attachment 121931
View attachment 121933
really appreciate if anyone can show how to solve this error.

Thank you.

The vendor's documentation for extracting LONGBINARYDATA exported via ODBC says:

There have been comments that this method doesn’t work from Access. This is a problem with Access. The type LONGVARBINARY type is a proper ODBC standard type. Access however only handles this type if it’s an Embedded OLE Object. If it were an OLE Object anyone wishing to access the data would have to strip off the OLE headers or go through the OLE object for access. This would also add the overhead of creating the headers, and due to the nature of the ODBC call API this might occur several times for each RawData field, and the performance hit would be large. To actually unpack a packed OLE Object would require many OLE API calls. The OLE format is undocumented so stripping the OLE Headers themselves would be dangerous because Microsoft can change the format on a whim. The problem of not accessing the data in the field is therefore a Microsoft bug and cannot be corrected without hamstringing a programmatic user of the ODBC driver.

So alternate solutions could be:

1. If possible, create a view on the server of the Raman spectroscopy data you want to extract and link to that view. The view will appear as a linked table in your Access frontend.

2. Download the BLOB file and access it locally instead of using ODBC. This avoids the OLE overhead and ensures data portability.

3. Use SQL Server instead of the native Access backend. SQL Server is able to handle standard LONGVARBINARY types correctly via the Microsoft ODBC Driver for SQL Server.

 

[Irwan]

hello @BlueSpruce,

1. Yes, the worst case is i will access the rawdata from access frontend then copy and paste the rawdata one by one.

2. How to download the BLOB file? do i need additional tools for that? Avoid OLE seems better than exporting from access frontend.

3. I heard some people that i have consulted before said that SQL can read Long Binary Data, but no similar stuff. But i will try to ask the IS department if they can do this.


Thank you.

 

[BlueSpruce]

hello @BlueSpruce,

1. Yes, the worst case is i will access the rawdata from access frontend then copy and paste the rawdata one by one...

BLOB's are large objects, so I would avoid using ODBC to read in the entire raw object, and then have to strip out the OLE header. Since you only need those top values, try to get IS to create a server side view on those values so you can link to the view from the Access FE and then use a passthru query to grab them. It's better to do as much heavy lifting possible on the server side to minimize network traffic.