SQL Serverr backend Access Frontend with linked tables unable to add records

Other SQL Server Topics
1

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.
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, dbSeeChanges)

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

Not something I know much about, but your code (I only glanced at it, so may have wrong end of the stick ...) looks like you are storing the image data in chunks:

For iChunks = 1 To lChunks
ReDim aChunk(conChunkSize)
Get nHandle, , aChunk()
rs("XLargeImage").AppendChunk aChunk()
lOffset = lOffset + conChunkSize
DoEvents
Next iChunks

Back in Old Versions of SQL the IMAGE datatype (and also TEXT for any string columns more than 8,000 characters) had to be stored in that way. But nowadays that isn't necessary because of new datatypes varbinary(MAX) (and varchar(MAX) for large strings). So if you are using IMAGE datatype (which is deprecated) change that to varbinary(MAX) instead.

(There may still be limitations because of ODBC etc. layer between your APP and SQL, or if "that's the way that Access does it" etc.)

Make sure you use separate tables for Images (don't put the Employee image in the Employee table, use a separate table joined on EmployeeID), and put that in separate file group

Worth just posing the question of whether (in your situation) its a good idea to store the images in the DB at all. There are pPROs and CONs which you can Google, but the main ones that occur to me are:

If images are large / numerous they bloat the database, stored in all backups, and moved around with the DB (e.g. a DEV, TEST, QA versions of the DB). But that can be a good thing too - the backup always contains all the images :slight_smile:

If you want to use the image on a Web Page it is mostly likely to be more efficient just to have it on disk so the URL can retrieve it from there.

Putting an image IN the DB means that it can be part of a transaction - so the Employee AND Image are wither both there, or neither are, you won't get someone accidentally deleting the image from disk, or there being an Image left over when an Employee record is deleted. But I think that SQL FILESTREAMS can also achieve that (with images on Disk)

If you put the image on disk, instead, then just store the path/filename in the DB (that is short, so would be OK to put IN the Employee table).

I've seen articles that say that images up to 256K stored in DB is good, and above 1MB consider storing in files. For images only used on Web Pages / APPs I would still favour the files approach.

The way I handle that (but don't know how easy that is to code-behind a button in Access?)

Let's say that my PKey is EmployeeID and the current record I am looking at is EmployeeID=1234

To get the next record I would do

SELECT TOP 1 EmployeeID, EmployeeName, ...
FROM Employees
WHERE EmployeeID > 1234
ORDER BY EmployeeID

Make sure (for performance) that you have an index on EmployeeID - usually in this situation that will be the Clustered Index, but doesn't have to be of course.

3

Kristen,

Thank you for your reply.. Firstly let me state that my code is on point and executes flawlessly against an Access table without issue. It is not the wrong end of the stick. AppendChunk is used to write binary data to a field and GetChunk is used to retrieve it. I DID NOT ask for a critique of my coding methods. So please keep your comments to yourself since you obviously know very little to begin with. Furthermore I am tired of people giving me their opinions about storing the images in the database instead of pointing to them on the drive, I do it this way because that is the way that I WANT TO DO IT. I'm not interested in your opinions about how I do things. Thanks for nothing.

4

I didn't give you one. I merely said that that WAS the way it USED to work with SQL (I did NOT say Access) when IMAGE was the only available datatype, also that IMAGE datatype is deprecated and AS SUCH you a) SHOULD use varbinary(MAX) instead and b) might need to use a different process i.e. without the Chunking. I also said "Not something I know much about" so you may indeed find that you can, still, use Chunking methods with varbinary(MAX) datatype columns - although presumably if it still worked the old way, in new versions of SQL, your code would just work as-is, and you wouldn't have a problem that needed solving.

Anyway, seeing as you aren't interested in my offer of advice I'll use my time on someone who is.