Updating current Access Dbase

[berger01]

I have developed a 2010 access Database that is successfully being used in all 50 States for the last 3 years. It’s time to improve the database and add features. I have some general questions before I start. Each database resides locally on each Offices server. The databases are split and multiple users in each office use the application. Once distributed, I (Developer) will not have any access to the BE. The improved database will be distributed and then split once again. I plan on writing code to import all tables form the old to the new.

• Importing data from old dbase to new should be easy, but I'm concerned about how to bring attachments over to the new. Any thoughts would be helpful?
• Any thoughts on possibly upsizing to an SQL Server? Each office is equipped with SQL. Are there any advantages to upsizing to SQL. The current size of each database is about 200MB

Thanks in advance for any comments.

[rpeare]

what do you mean by 'bring attachments over' are you talking about updates to table designs?

If so you'd do that with an ALTER TABLE SQL statement. If you're talking about brand new tables you would likely have to cycle through the tabledefs of the 'update' portion of your data to import those ones fresh.

200mb is pretty small to be thinking about SQL, particularly if they haven't been compacted/repaired in a while I wouldn't start thinking about SQL until it's an issue with the office having multiple concurrent users.

[berger01]

I need to know how to move "Files" located in the Attachment Fields within each table. Importing the data from the old tables to the new tables is understood. Its the attached files I am concerned about. Is there some VBA code that will do that? Based on this discussion, I don't see a need to move to a SQL Server, thanks for the discussion.

[rpeare]

When I do this sort of thing (very infrequently) I don't store the document as an attachment. I store it as a path to the stored document, but that being said I also usually put the 'extra' files in the same location as the database but in a different subfolder. I don't know what your update process is going to look like but part of it could be to unzip these files to a 'static' location (currentproject.path & "\supportfolder\"). then update your table with the actual path of the local currentproject.path.

[NTC]

there is zero advantage to moving to SQLserver for the back end file if it remains simply holding tables and you are well under the 2g file size. The SQLserver advantage involves back end has data actions (something included in Access for the web forms aspects of Access 2010 and beyond).

I also used linked documents rather than the attachments field types; set up a quickie trial and copy a few across from 1 db to another - it may work.