Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185

    Question Unanswered: MS Access 2007 db upsizing t0 SQL Server Express 2008

    Hi Folks,
    I have an access 2007 data base for document control and it saves the received documents in a table in a attachment field type, I tried to upsize it to SQL Server 2005 but after upsizing realized that this field type is not supported by 2005 so I downloaded the SQL Express 2008 and installed it as it has this enhancement but Access 2007 doesn’t recognize the server for upsizing.
    I appreciate you help
    Thanks

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Don't store the document in the database, store the path to the document instead.
    George
    Home | Blog

  3. #3
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Why, may I ask? Is it because many apps don't know how to handle documents stored in a db? Or because there is some "best practice" documentation of an "expert" with questionable "experience" who decided to voice his/her opinion in the area where he/she has the least amount of knowledge? Hundreds of apps store binary objects in image field, thousands are using text/ntext to store textual info in a database, and few good ones don't have any issues. Blindly (no reference to blindman) stating that one needs to store documents in file system is at least premature, because you don't know how the app is using those documents (at the very least).
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Robert, George was just giving an opinion based on his own experiences and preferences. Its expected that others may have different opinions and advice and post their arguments as well. Thats what makes it a forum.

    Most of the dbas on this forum and others (such as sqlteam.com) recommend against storing binary objects in the database, usually for administrative reasons. But this is not a hard and fast rule, and storing these objects in the database is supported in all SQLSVR versions I can remember.

    I have not developed apps to do this, so I cannot personally comment on whether this is a good idea, but since you are upsizing what is presumably a small (MS Access) existing application, it may be appropriate for your situation.

    Perhaps Robert could give the poster some advice on the best datatype to use, and the best practices for storing and retrieving the data?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    The datatype to use is dependent on what kind of documents are intended to be stored, but to accomodate for ANY you would probably need IMAGE. However, there is a problem upsizing Access db with Attachment field, because in Access you can have more than 1 document stored per row, and Access handles differentiation between documents for you. In SQL you will have to handle it yourself, unless...Unless you break that table into AttachmentMaster and AttachmentDetail, where you would have as many rows in AttachmentDetail as there are attached documents for a unique row in the source.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Thanks Guys/gals, I shall mention that this data base has been already used with attachements and I can not decide in this point to forget the files attached to it so I have to upsize it with attachements,I don't have problem with number of documents stored per row as it is onle one pdf file in each row, I have two questions:
    1-Is it possible to upsize with SQLEXPRESS 2008? as my access 2007 doesn't recognise the server, how should i check if the server works properly or any other idea to get the access to recognise the server?
    2-Does SQl support the pdf attachement? and should I do any thing special? or it will be upsized automatically like other field types?

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Hi Robert,

    thank you for questioning my post, but please refrain from doing it in some a defamatory manner.

    The most obvious reason (IMO) for storing the information in the database over the file system is because you gain immediate integrity and won't end up with orphaned database entries or files. This method leaves the work up to the database engine instead of front-end logic; and I know what I trust more!

    ...I can't throw a developer very far!

    What I'm saying is that I can appreciate why someone would want to store objects within the database, but I have yet to see a successful implementation of this which has somewhat biased my opinion.
    George
    Home | Blog

  8. #8
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    MS has had a number of attempts to make a filesystem out of sql server, all failed. Most recently WinFS: http://en.wikipedia.org/wiki/WinFS

    doesn't mean you can't succeed though!

  9. #9
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I guess in 2008 this is solved with the new FILESTREAM type.

    http://blogs.technet.com/dataplatfor...and-files.aspx

  10. #10
    Join Date
    Feb 2004
    Location
    Alpine Califormia
    Posts
    1,789
    Quote Originally Posted by georgev
    Don't store the document in the database, store the path to the document instead.

    I have an ADP its Access 2003 on the front end and SQL Server 2005 as the engine, and I posed that same question to the guys here. They said to store the path not the entire PDF file, would be interesting see a database that does store them though. I'm just curious thats all
    Last edited by desireemm; 04-27-08 at 18:38.

  11. #11
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by blindman
    Robert, George was just giving an opinion based on his own experiences and preferences. Its expected that others may have different opinions and advice and post their arguments as well. Thats what makes it a forum.
    That's the reason why I "xame back". I like to share
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  12. #12
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Quote Originally Posted by georgev
    Hi Robert,
    thank you for questioning my post, but please refrain from doing it in some a defamatory manner.
    Actually, I never mean my comments to be defamatory. I do try to make them sarcastic
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  13. #13
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    Sorry but I think my questions vanished, I appreciate if anybody can give a hint regarding my questions:
    1-Is it possible to upsize with SQLEXPRESS 2008? as my access 2007 doesn't recognise the server, how should i check if the server works properly or any other idea to get the access to recognise the server?
    2-Does SQl support the pdf attachement? and should I do any thing special? or it will be upsized automatically like other field types?
    Cheers

  14. #14
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Open Configuration Manager and make sure that either Named Pipes or TCP/IP protocol is enabled. In Surface Area Configuration for Services and Connections make sure that Remote Connections is enabled.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •