Migrating Access tables to SQL

I have a MS Access database that has one table that has an attachment field.  The database has been in use for some time so there are several records in the table that have an attachment.

The issue now is we want to migrate the tables in the database to SQL.  But SQL databases don't accommodate an attachment field.  Is there a way to migrate to SQL without losing the attachment data somehow?  I guess I'm hoping someone has run into this before and has a magic solution.
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
No.  SQL Server does not support attachments.  You would need to change the data type in Access to something SQL Server can support and then convert the data.  Of course, that means changing your forms, queries, etc.  Not sure why MS went this route but it was pretty stupid to add data types to Access that SQL server can't support.  Of course, they are trying to push us all to use SharePoint so that may have had something to do with it.

A word of warning to everyone.  The three new data types added with A2007 CANNOT be converted to SQL Server so use them at your own risk.   SQL server also doesn't support the Hyperlink data type which I think was added earlier.
0
Jeffrey CoachmanMIS LiasonCommented:
...or multivalued datatype, ...or OLE, ...or memo/rich text (HTML)
...no "direct" support in SQL, to my knowledge...
:-(

There is code out there to "extract" the attachment, ...
Then you can store them in a folder and "Link" to them. (the preferred method)
See this link:
http://www.experts-exchange.com/Database/MS_Access/Q_28507558.html

JeffCoachman
0
Jeffrey CoachmanMIS LiasonCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Nick67Commented:
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.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you prefer to store your images directly in the database, you can do so in a SQL Server BLOB field. There's still no one-step solution to do it, so you'd use Nick's suggestion to loop through the records. You can use the DAO example below to "save" the attachment to a file, and then you'd use the GetChunk/WriteChunk methods to move them to SQL Server.

Note that SQL doesn't have the troubles normally associated with storing images in Access. You can still use the file path method suggested by Nick earlier, of course, if that better suits your needs.

To get to the data in a multivalued field, you have to use code like this:

Dim rst As DAO.Recordset
Set rst = Currentdb.OpenRecordset("YourSQLhere")

Do Until rst.EOF
  Dim rst2 As DAO.Recordset
  Set rst2 = rst("YourAttachmentField").Value
  '/rst2 will be the recordset containing the Attachment
  rst2("FileData").SaveToFile, "Path to save"
  rst.MoveNext
Loop

Using DAO with multivalued fields: https://msdn.microsoft.com/en-us/library/office/ff821054.aspx
Saving Attachments to File: https://msdn.microsoft.com/en-us/library/bb258184%28v=office.12%29.aspx
How to read/write BLOBs using GetChunk: https://support.microsoft.com/en-us/kb/194975
0
SteveL13Author Commented:
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

Open in new window

0
Nick67Commented:
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

Open in new window

0
SteveL13Author Commented:
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
0
Nick67Commented:
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?
0
SteveL13Author Commented:
Still can't compile on:

Set dbs = CurrentDb

And this line is red now:

If (rstAttachments.BOF=True AND rstAttachments.EOF = True)= False
0
Nick67Commented:
If (rstAttachments.BOF=True AND rstAttachments.EOF = True)= False Then
My bad.
Air-code doesn't have a syntax checker :(

Still can't compile on:
 Set dbs = CurrentDb


Did that line ever work?
If you comment out everything but
    Dim dbs As DAO.Database
    Dim rstSource As DAO.Recordset
    Dim rstAttachments As DAO.Recordset
    Set dbs = CurrentDb

does it work?
If not, you have something odd going on.
Perhaps a broken reference.
And, we'll see if anyone else chimes in, but the attachments recordset, that may have to be something other than a straight DAO recordset, too.

Upon further research, you'll need a recordset2 object.
Nice example here
https://msdn.microsoft.com/en-us/library/office/ff197737.aspx

Adapt the code from there as required
Public Function SaveAttachments(strPath As String, Optional strPattern As String = "*.*") As Long
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset2
    Dim rsA As DAO.Recordset2
    Dim fld As DAO.Field2
    Dim strFullPath As String
    
    'Get the database, recordset, and attachment field
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblAttachments")
    Set fld = rst("Attachments")
    
    'Navigate through the table
    Do While Not rst.EOF
    
        'Get the recordset for the Attachments field
        Set rsA = fld.Value
        
        'Save all attachments in the field
        Do While Not rsA.EOF
            If rsA("FileName") Like strPattern Then
                strFullPath = strPath & "\" & rsA("FileName")
                
                'Make sure the file does not exist and save
                If Dir(strFullPath) = "" Then
                    rsA("FileData").SaveToFile strFullPath
                End If
                
                'Increment the number of files saved
                SaveAttachments = SaveAttachments + 1
            End If
            
            'Next attachment
            rsA.MoveNext
        Loop
        rsA.Close
        
        'Next record
        rst.MoveNext
    Loop
    
    rst.Close
    dbs.Close
    
    Set fld = Nothing
    Set rsA = Nothing
    Set rst = Nothing
    Set dbs = Nothing
End Function

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
To all... I am still experimenting with all of this.  I'll be back in a day or two I expect.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.