Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    in the middle of figuring this one out, but i thought i'd pass it by the loungers...

    What i want to do is, using Access, create a procedure that creates a new, archive-ready access db and load that created db into SQL Server. this would be a nice win as the archived dbs wouldn't be hanging out in some file server somewhere. if it has to be written to a directory first, that's ok - i'd just build in some delete routine.

    I've previously implemented file uploading in ASP so there's probably a solution out there. the process should be invisible to the user and would happen based on some user action. (Actually, a lot of things will happen when the button is clicked.)

  2. #2
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    [quote name='steve_skelton13' post='795901' date='01-Oct-2009 20:07']in the middle of figuring this one out, but i thought i'd pass it by the loungers...

    What i want to do is, using Access, create a procedure that creates a new, archive-ready access db and load that created db into SQL Server. this would be a nice win as the archived dbs wouldn't be hanging out in some file server somewhere. if it has to be written to a directory first, that's ok - i'd just build in some delete routine.

    I've previously implemented file uploading in ASP so there's probably a solution out there. the process should be invisible to the user and would happen based on some user action. (Actually, a lot of things will happen when the button is clicked.)[/quote]

    Steve, why not let SQL Server to the donkey work, after all that is what it is built for? Couldn't you just create a DTS package that is able to pick up your access database tables and import them into the server. Just thoughts
    Jerry

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I want to keep this all in Access as that is the primary (actually only) UI for the project. I also want to allow people who don't necessarily want to deal with anything more than clicking a button to manage the entire process under consideration, so no SQL DTS runs, if I can help it...

    I suppose I could create a button in Access that runs some DTS package on SQL Server - that would be fine. Not sure how to do that, tho.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2002
    Location
    A Magic Forest in Deepest, Darkest Kent
    Posts
    5,681
    Thanks
    0
    Thanked 1 Time in 1 Post
    Just to help my understanding is it an Access front end with a SQL back end?
    Jerry

  5. #5
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    not even that much - it's an access project with forms and VBA - no data.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    OK - now I'm confused. From your description I think you have an ADP which connects directly to a SQL Server database. So why do you want to create a new access database in SQL Server? Access databases, whether they are .mdb, .mde, .adp, .ade, .accdb, or .accdp, are all stored as files in a directory systems of some sort. I suppose in theory, you could store an access database in a binary object field in SQL Server, but I don't see any advantage in doing that, as it has to be a file in order to use it....
    Wendell

  7. #7
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    i know this discussion has moved to another thread, but in case anyone is wondering how to do it, here's a way:

    1. setup an ODBC connection to SQL Server
    2. create a pass-thru query
    3. before writing the query, access Properties to specify the ODBC file, which is basically a connection string. You can opt not to store the password in the query (it will be stored in plaintext...). If you don't want a response (that is, a recordset), set Return Records to "No".
    4. write something like this:

    INSERT into dbo.<SQLServerTable>(<TableFieldSetToVarchar(Max)> )
    SELECT * FROM OPENROWSET(BULK N'\\<computername>\<path\<path>\<TARGET MDB FILE>', SINGLE_BLOB) as Document;

    NOTE: if SQL Server is not running on the same machine as the target file, you must use UNC.

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi Steve, could you explain the meaning of TableFieldSetToVarchar(Max)?

    [quote name='steve_skelton13' post='796640' date='06-Oct-2009 19:17']i know this discussion has moved to another thread, but in case anyone is wondering how to do it, here's a way:

    1. setup an ODBC connection to SQL Server
    2. create a pass-thru query
    3. before writing the query, access Properties to specify the ODBC file, which is basically a connection string. You can opt not to store the password in the query (it will be stored in plaintext...). If you don't want a response (that is, a recordset), set Return Records to "No".
    4. write something like this:

    INSERT into dbo.<SQLServerTable>(<TableFieldSetToVarchar(Max)> )
    SELECT * FROM OPENROWSET(BULK N'\\<computername>\<path\<path>\<TARGET MDB FILE>', SINGLE_BLOB) as Document;

    NOTE: if SQL Server is not running on the same machine as the target file, you must use UNC.[/quote]

  9. #9
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='grovelli' post='796699' date='07-Oct-2009 06:49']Hi Steve, could you explain the meaning of TableFieldSetToVarchar(Max)?[/quote]

    just specifying that the column data type where you store the db should be Varchar(max)

  10. #10
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Note that the Varchar(max) works in SQL Server 2005 and 2008, but in 2000 the limit for varchars is 8000 bytes and Access databases won't fit in that case. However the text and image types could be used, as they generally support up to about 2GB.
    Wendell

  11. #11
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I believe IMAGE was (or is) an option in 2000 for binary data.

Posting Permissions

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