Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Jan 2004
    Posts
    9

    Unanswered: I need some advice before starting VB/SQL Project

    I am just in the process of learning VB.NET so that I can upsize an access database that I developed for my company.

    I need advise on two subjects:
    First, I am going to be starting a new VB.NET / SQL project using MSDE installed on my development computer. What do I need to do to allow me to easily deploy the program on several workstations and a dedicated server once development is finished?

    Second, I would like to have a transitional period where both the VBA in the current MDB and the new VB.NET project access the same data. The one solution that I came up with was creating queries on the SQL server that pointed to the MDB, then once I am comfortable with the VB.NET end of things I would transfer the data it's self to the SQL server and change the queries to point to the new data location... However I have no idea weather or not this will work or if it is even close to being a good idea.

    Any advice would be greatly appreciated.

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    I wouldn't waste my time on developing in Access and then moving to SQL Server. Just start with the latter!

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Michael Kaiser

    Brett Smith

    Geez I thought I was unique....

    Anyway, take rdjabarov's advice...

    You can easily set up an Access mdb and link the sql server tables to it...

    Don't bother with Jet...or don't you have sql server installed yet?

    Or is it an existing application already?

    How big is the database (or how big will it be?)

    Also, you don't mention anything about stored procedures....

    Do you plan on using them...it would be a big plus, especially for you're design implementation...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Absolutely. Creating queries in SQL Server that point to data in a transitional Access database is going to cause you more trouble than just building the tables in SQL Server to begin with.

    Hey, I like Access a lot, but there is really no point in using it in your development if it is not going to be part of your final solution.

    If your data is already in Access, then it is worth trying the Upsizing wizard. I've used it once or twice and I seem to recall that it did a decent job. If you need a transitional application, then move your existing forms and code into a Microsoft Access Data Project that is tied directly to your SQL server database.
    If it's not practically useful, then it's practically useless.

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

  5. #5
    Join Date
    Jan 2004
    Posts
    9
    Unfortunately I am already up to my eyeballs in Access
    This is at the moment the primary issue tracking system for the small consulting firm that I work for. Hundreds of contacts, thousands of related records... and it has only been in use for about 6 months.

    What I need to do is develop the project in vb and slowly transfer functionality over as modules get debugged.

    To answer the other question about stored procedures, I am definitely going to be using them.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I'm a little confused (and that's on a good day)...

    Is the data centraly located, or does each client have their own copy of the data?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Then again, I think you would be best off if you transfered the data to Microsoft SQL server and converted your Access database into an Access Data Project while you transition to your final solution.
    If it's not practically useful, then it's practically useless.

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

  8. #8
    Join Date
    Jan 2004
    Posts
    9
    The data is centrally located in a mdb that only contains tables and each client has their own MDB file which has all the forms reports and VBA and links to the tables on the main database

    I won't even begin to try to explain how this configuration came about, just keep in mind that decisions like that came about via committee and I (the actual developer) was not invited.

  9. #9
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Well, that's not a bad configuration for a distributed Microsoft Acces solution, but it does make your upsizing more difficult.

    You could still develop an Access Data Project file and distribute it to your users. If you import your forms and modules it should look identical to them. Somewhere, you are going to have to bite the administrative bullet and come up with the transition strategy that best suits your business.
    If it's not practically useful, then it's practically useless.

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

  10. #10
    Join Date
    Jan 2004
    Posts
    9
    I guess at this point I am going to look into an ADP, what I was hoping to avoid was spending any more time in access when it's not in the final plan. I know that the upsize wizard will not work for most of my forms and two of my tables, but I may just have to do it and get it over with

  11. #11
    Join Date
    Jan 2004
    Posts
    9
    Just another thought, would it make senct to just migrate the data to SQL and then change the links in the client MDB files to point to the SQL tables via an ODBC connection rather than actually transfer everything over to an ADP?

  12. #12
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I would....

    a data source is a data source....

    Only downside depends on the complexity of the forms/reports...

    could be a lot slower depending on the # of connections opened by a form or report..
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Sure. Just make sure your security is good. Also, chances are good that you performance will be significantly slower than if you used an ADP interface.
    If it's not practically useful, then it's practically useless.

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

  14. #14
    Join Date
    Jan 2004
    Posts
    9
    This is looking like my best option.

    The last question on that front would be does anyone know if I like an Access MDB to SQL via ODBC can I use a SQL View for the source and meke it appear to Access to be a linked table?

    I would only want to do this for two of the tables, because I would like to make some changes to the table field names.

  15. #15
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yeah...check it out...they'll appear just like tables...

    Just make sure that the view is updatable...otherwise it'll be a problem...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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