Migrating Access tables to SQL

The whole Idea behind the attachment datatype was that the files would not "bloat" the database.
Then you could click the attachment and load the image.
That is great for a few small text files, but for tables with "a lot" of records, ...and larger files, ...the db will still grow in size quite considerably.

SQL Server just stores "Data", so you cant really "activate" anything directly.
Linking was always the better option IMHO.
...here is a simple sample db
http://filedb.experts-exchange.com/incoming/2011/03_w12/431404/Access-BasicLinkFormReportImages.mdb

JeffCoachman

Is there a way to migrate to SQL without losing the attachment data somehow?  
Yes
I guess I'm hoping someone has run into this before and has a magic solution.
No.

It's not magic, it's doing what was sane in the first place.
it requires VBA
You'll need to fire up a recordset with the attachment field and then export the attachments to files
You'll need to alter the table to take a full path to the new files
You'll then need to gut the functionality that used the attachment fields and replace it with something that can use the filepaths instead.

If it's images and you are A2010+ you'll have magic as an Image control can be bound to a filepath in the those versions.
If it's Word, Excel, PDF, the fun is just beginning.

Thanks to all so far.  But I've copy/pasted/altered the following code in a module.  But when I run the module I get "No Current Record" error.

Sub InsertAttachments()
    Dim dbs As DAO.Database
    Dim rstSource As DAO.Recordset
    Dim rstAttachments As DAO.Recordset
    Set dbs = CurrentDb
    Set rstSource = dbs.OpenRecordset("Rockford Charitable Games mail list")
     
    Do Until rstSource.EOF

    Set rstAttachments = rstSource.Fields("ScannedIDcard").Value
                
    rstAttachments.MoveFirst
    While Not rstAttachments.EOF
                
    rstAttachments.Fields("FileData").SaveToFile CurrentProject.Path & "C:\ScannedIDCard"
    rstAttachments.MoveNext
                
    Wend
                
    rstSource.MoveNext
    
    Loop
    
 End Sub

Select allOpen in new window

When in doubt, force a full populating of the recordset and SEE how many records you've got
AfterSet rstSource = dbs.OpenRecordset("Rockford Charitable Games mail list")

add in
rst.movelast
rst.movefirst
MsgBox rstSource.RecordCount

That'll tell you how many records you're dealing with.
Now, you have no handling of the situation where there's no attachment.
If there's no attachment
rstAttachments.MoveFirst
will bomb

So, try this, after you fix the problem noted in the code itself!

    Dim dbs As DAO.Database
    Dim rstSource As DAO.Recordset
    Dim rstAttachments As DAO.Recordset
    Set dbs = CurrentDb
    Set rstSource = dbs.OpenRecordset("Rockford Charitable Games mail list")
    rst.movelast
    rst.movefirst
    MsgBox rstSource.RecordCount 'check that rstSource is populated   
    Do Until rstSource.EOF
         Set rstAttachments = rstSource.Fields("ScannedIDcard").Value
         If rstAttachments.BOF and  rstAttachments.EOF = False 'check if there are attachments first                
            'if there are, then
             rstAttachments.MoveFirst
             While Not rstAttachments.EOF 'run 'em down and save them out
                
                 'this next line will be bad -- there's nothing to differentiate one file from the next!  They'll overwrite each other.  Add some sort of differentiator!
                  rstAttachments.Fields("FileData").SaveToFile CurrentProject.Path & "C:\ScannedIDCard"
                 '*********** The line above is likely poor syntax***************
                 'SaveToFile requires a unique valid path for each file
                 'CurrentProject.Path & "C:\ScannedIDCard" is neither unique nor valid
                 '**************************
                  rstAttachments.MoveNext
                
             Wend
	End IF
                
        rstSource.MoveNext
    
    Loop

Select allOpen in new window

When I paste the latest code into the module this line turns red:

If rstAttachments.BOF and  rstAttachments.EOF = False 'check if there are attachments first

Plus when I try to compile I get Invalid outside procedure on

Set dbs = CurrentDb

You can try
If (rstAttachments.BOF=True AND  rstAttachments.EOF = True)= False
That's more explicit and perhaps required.

and my bad
   rst.movelast
    rst.movefirst
should be
   rstSource.movelast
    rstSource.movefirst

The other bit is a mystery.
Was it working before?

Still can't compile on:

Set dbs = CurrentDb

And this line is red now:

If (rstAttachments.BOF=True AND rstAttachments.EOF = True)= False

To all... I am still experimenting with all of this.  I'll be back in a day or two I expect.