ODBC-MS Access Connection Call Failed Error 3146 (1 Viewer)

I

Irwan

New member
Local time
Today, 14:59
Joined
Oct 22, 2025
Messages
4
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.
1761095194280.png

1761095601960.png

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

Thank you.
 
What does the Long Binary Data holds, image, pdf, what?
 
hello arnelgp,

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

The goal is to extract the highlight yellow.

Really appreaciate for the help.
Thank you.
 
how many records does rs holds when you open the recordset? only 1?
after this line:
Code: 
Set rs = db.OpenRecordset("Select RawData, LineResolution From VibSpectra Where SpectraKey = ...)
insert this line to save the Rawdata to a file (RawData.bin)
Code: 
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).
 
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.
 
"..Here is the code provided by the vendors, but it keeps give me error.."

And the vendors, what says?
 
Irwan said:
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.
Click to expand...
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.

Code: 
' 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
 
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.
 

Attachments

Last edited:
Irwan said:
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.
Click to expand...
Add error trapping to your existing code.

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
 
Here is what ChatGPT has to say"
Code: 
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
 
Last edited:
Irwan said:
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.
Click to expand...

I don't understand
The vendor gives you an example that doesn't work?
 
Irwan said:
Do you have another example VB code on how to pull data from ODBC connection using another similar code OpenDatabase?
Click to expand...
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.
 
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.
 
You must log in or register to reply here.

Similar threads

N
How to deal with dynamic IP address in MS Access to MYSQL DB Maria
Replies
5
Views
720
MadPiet
M
arnelgp
Extracting Image from OLE field
Replies
1
Views
993
Jason Lee Hayes
Jason Lee Hayes
N
MS Access VBA Vs JAVA
Replies
2
Views
653
CJ_London
C
N
Solved Export data from Passthrough Query to CSV
2
Replies
20
Views
1,361
Gasman
Gasman
I
MYSQL Connection on AWS
Replies
6
Views
704
cheekybuddha
cheekybuddha

Users who are viewing this thread

Total: 2 (members: 1, guests: 1)
Back
Top Bottom