Results 1 to 2 of 2
  1. #1
    VSCurtis is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Dec 2014
    Posts
    65

    Using Linked SQL Server Database With Access Database ODBC Call Fail

    Hi Guys,

    I'm treading on unfamiliar territory here and could use some help. I have used the SQL Server Upsize Assistant Tool to upsize my Access database to SQL Server 2014 Express and have linked the tables to my Access database. I'm now trying to import my BLOB data back into the SQL Server tables since the tool did not import my data. I have a stand-alone procedure that I used to do this for my Access database. The code fails on the first record. It hits the recordset update statement and fails with an ODBC Call Fail error message. Since I've never attempted this type of thing before I am unsure what modifications I need to make to my code or other configuration adjustments I need to make in order for this to work. Additionally, I have a form in my Access database that I use for editing data record by record. In other word the form loads, it displays the first record, I click Edit, make my changes, click update and move to the next record and the process continues for each record. I would be grateful for any assistance you can provide in resolving this issue so I can move forward. I don't know how much help it will be, but here's the code I'm executing.

    Code:
    Public Sub InsertCardImagesAndKeys()
    Dim sPath As String
    Dim sFullPath As String
    Dim sFileName As String
    Dim sPrevFileName As String
    Dim sNextLetter As String
    Dim sNewKey As String
    Dim sCounter As String
    Dim nHandle As Integer
    Dim iPathIndex As Integer
    Dim iFileNameIndex As Integer
    Dim iFragmentOffset As Integer
    Dim iChunks As Integer
    Dim lOffset As Long
    Dim lSize As Long
    Dim lChunks As Long
    Dim aPaths(3) As String
    Dim aFileNames() As String
    Dim aChunk() As Byte
    Dim fs, f, f1, fc, s
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("tblCards", dbOpenDynaset)
    aPaths(0) = "E:\Yu-Gi-Oh\images\cards\xl\"
    aPaths(1) = "E:\Yu-Gi-Oh\images\cards\large\"
    aPaths(2) = "E:\Yu-Gi-Oh\images\cards\medium\"
    aPaths(3) = "E:\Yu-Gi-Oh\images\cards\thumbnail\"
    sNextLetter = "#"
    sCounter = sNextLetter & "_" & rs.Name
    Set fs = CreateObject("Scripting.FileSystemObject")
    Set f = fs.GetFolder(aPaths(0))
    Set fc = f.Files
    'Load All File Names Into Array
    For Each f1 In fc
       If IsArrayAllocated(aFileNames) = True Then
          ReDim Preserve aFileNames(UBound(aFileNames) + 1)
       Else
          ReDim aFileNames(0)
       End If
       aFileNames(UBound(aFileNames)) = f1.Name
    Next
    Debug.Print UBound(aFileNames) & " File Names Loaded."
    'Process All Files For Each Image Tyoe Lange Medium and Thumbnail
    For iFileNameIndex = LBound(aFileNames) To UBound(aFileNames)
       For iPathIndex = LBound(aPaths) To UBound(aPaths)
          sFileName = aFileNames(iFileNameIndex)
          sFullPath = aPaths(iPathIndex) & sFileName
          nHandle = FreeFile
          'Open File To Be Written To Table
          Debug.Print "Processing File: ", sFullPath
          Open sFullPath For Binary Access Read As nHandle
          If nHandle = 0 Then
             Close nHandle
             Debug.Print "Error Reading File: ", sFullPath
             Exit Sub
          Else
             'Did The File Name Change?  Then we're processing and new group of images
             If sFileName <> sPrevFileName Then
                sNewKey = NextKey(sCounter, sNextLetter)
                If sNewKey = "****" Then
                   sNextLetter = NextLetter(sNextLetter)
                   sCounter = sNextLetter & "_" & rs.Name
                   sNewKey = NextKey(sCounter, sNextLetter)
                End If
                rs.AddNew
                rs("CardID") = sNewKey
                rs("FileName") = sFileName
                sPrevFileName = sFileName
             End If
          End If
          lSize = LOF(nHandle)
          lChunks = lSize \ conChunkSize
          iFragmentOffset = lSize Mod conChunkSize
          'Which File Are We Reading Large, Medium or Thumbnail?  Path Value Tells Us
          Select Case iPathIndex
             'Write X Large Image Data
             Case 0
                If lChunks > 0 Then
                   For iChunks = 1 To lChunks
                      ReDim aChunk(conChunkSize)
                      Get nHandle, , aChunk()
                      rs("XLargeImage").AppendChunk aChunk()
                      lOffset = lOffset + conChunkSize
                      DoEvents
                   Next iChunks
                End If
                ReDim aChunk(iFragmentOffset)
                Get nHandle, , aChunk()
                rs("XLargeImage").AppendChunk aChunk()
             'Write Large Image Data
             Case 1
                If lChunks > 0 Then
                   For iChunks = 1 To lChunks
                      ReDim aChunk(conChunkSize)
                      Get nHandle, , aChunk()
                      rs("LargeImage").AppendChunk aChunk()
                      lOffset = lOffset + conChunkSize
                      DoEvents
                   Next iChunks
                End If
                ReDim aChunk(iFragmentOffset)
                Get nHandle, , aChunk()
                rs("LargeImage").AppendChunk aChunk()
             'Write Medium Image Data
             Case 2
                If lChunks > 0 Then
                   For iChunks = 1 To lChunks
                      ReDim aChunk(conChunkSize)
                      Get nHandle, , aChunk()
                      rs("MediumImage").AppendChunk aChunk()
                      lOffset = lOffset + conChunkSize
                      DoEvents
                   Next iChunks
                End If
                ReDim aChunk(iFragmentOffset)
                Get nHandle, , aChunk()
                rs("MediumImage").AppendChunk aChunk()
             'Write Thumbnail Image Data
             Case 3
                If lChunks > 0 Then
                   For iChunks = 1 To lChunks
                      ReDim aChunk(conChunkSize)
                      Get nHandle, , aChunk()
                      rs("ThumbnailImage").AppendChunk aChunk()
                      lOffset = lOffset + conChunkSize
                      DoEvents
                   Next iChunks
                End If
                ReDim aChunk(iFragmentOffset)
                Get nHandle, , aChunk()
                rs("ThumbnailImage").AppendChunk aChunk()
          End Select
          Close nHandle
       Next iPathIndex
       rs.Update
       rs.Bookmark = rs.LastModified
    Next iFileNameIndex
    Debug.Print rs.RecordCount; " Records Processed: Processing Complete"
    rs.Close
    Set rs = Nothing
    End Sub


  2. #2
    NoellaG's Avatar
    NoellaG is offline VIP
    Windows 7 Access 2010 (version 14.0)
    Join Date
    Jun 2010
    Location
    Belgium
    Posts
    1,035
    Hi, first of all you need to check what field type you have in SQL server, probably a varbinary(max). If you need to store pictures > 2 GIG you need the filestream. Maybe this link can help: https://technet.microsoft.com/en-us/.../bb933993.aspx

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 12-17-2015, 07:01 AM
  2. Replies: 14
    Last Post: 07-29-2013, 02:33 PM
  3. Replies: 3
    Last Post: 08-06-2012, 10:38 AM
  4. Replies: 2
    Last Post: 03-29-2012, 07:45 AM
  5. ODBC Call Fail - Only when totals applied
    By wvuatrrd in forum Queries
    Replies: 3
    Last Post: 11-04-2010, 12:52 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums