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