Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Feb 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Synchronizing an Access 2000 .MDB with SQL Server (Access 2000)

    Hi all,

    I have a couple of linked databases I use for work, and as part of a new project (for entertainment). I

  2. #2
    Silver Lounger
    Join Date
    Jan 2001
    Location
    Indianapolis, Indiana, USA
    Posts
    1,862
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Synchronizing an Access 2000 .MDB with SQL Server (Access 2000)

    Hi Tim,

    Is there any reason you couldn't write this functionality into a web application? This sounds like the obvious (although, not the easiest) solution.

    This can be a big undertaking, depending on the complexity of information. However, if your data is relatively one-dimmensional, it should be rather easy.

    Hope this helps!

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Synchronizing an Access 2000 .MDB with SQL Server (Access 2000)

    Another approach might be to upsize you local copy to the MSDE, and then link your front-end to the MDSE tables with ODBC. Sort of a kludge, but it works in a pinch, and then you can use SQL Server replication, expecially if it's a one-way update. That means of course that you need to learn lots about SQL Server however. Are you sure you need SQL Server? We run some web apps using Access when the updating is done locally, and we simply copy the .mdb file down and back up when we want to make updates. If lots of people are hitting the site, SQL Server would certainly be a better choice, but if it's low traffic, Access works just fine.

    On the other hand, Mark's suggestion bears investigation. Doing database that update with web pages isn't that hard, especially if you are using .NET.
    Wendell

  4. #4
    New Lounger
    Join Date
    Feb 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Synchronizing an Access 2000 .MDB with SQL Server (Access 2000)

    Not a bad idea, but I'm only adding plants in the access app, not directly on the web page. (It'll basically be an online catalog) Any other ideas? Thanks for your help!

  5. #5
    New Lounger
    Join Date
    Feb 2001
    Posts
    19
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Synchronizing an Access 2000 .MDB with SQL Server (Access 2000)

    Not a bad idea, but I don't have access to the SQL Server replication service, I'm only running the SQL engine locally that comes with the .NET framework. I'd like to think the web site will be so popular I'll need SQL Server, but really.... I can't really link the tables with MSDE, as I'm working both at home and at work. Thanks for your help, and I'm all ears for more suggestions. It all comes down to the fact I don't type well, and anything to ease data input is just wonderful.

    Tim

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Synchronizing an Access 2000 .MDB with SQL Server (Access 2000)

    If you are running the SQL Server developer version that comes with the .NET framework, then you can certainly link to it with ODBC data source connections, and administer it with local Access forms and so on. The fundamental question is where the .NET version of your database is going to be hosted. Are you running an Internet Server at home, or are you using an ISP service?
    Wendell

  7. #7
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Minneapolis, Minnesota, USA
    Posts
    262
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Synchronizing an Access 2000 .MDB with SQL Server (Access 2000)

    This might be a bit kludgy, but....

    You'd need to create:
    (1) A NewPlants table in your Access db
    (2) A stored procedure in your SQL db that accepts input parameters of Plant ID, Plant Name, Plant Address and so forth. This stored proc then appends the record to the SQL Server Plants table.
    (3) VBA code that (a) connects to the SQL db, ([img]/forums/images/smilies/cool.gif[/img] loops through the Access NewPlants table and for each record it finds passes the necessary info to the stored proc closes the connection upon completion, (d) deletes records from the NewPlants Access table.

    As you add new plants to your Access Plants table, they also get written to the NewPlants table. You don't have broadband, so when you have some free time on your dial up connection, you launch the process that calls the VBA you wrote for step 3. Hmmm...did I say a bit kludgy?!

    I think you'll be better off following the path that Mark and Wendell are outlining for you. Also, I'd suggest that if Office Developer is out of the ball park, financially speaking, licensing SQL Server for a production commercial application is, like, waaaaaay out of the park! By the way, MSDE, the SQL Lite that ships with .NET, isn't allowed for production databases, and caps use to only 5 concurrent connections.

    Good luck!
    <font face="Comic Sans MS"><font color=blue>~Shane</font color=blue></font face=comic>

Posting Permissions

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