Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2

    Unanswered: Upsizing problems - Access 2000 to SQL Server 2000

    Hi guys

    What started out as a small mini-project now has taken on a life of its own! I recently had to upsize an Access 2000 database to SQL Server 2000 due to problems caused by the network architecture. The upsizing went fine - I had a new SQL database on the dedicated server, and a new .adp file with my front end objects.

    When I checked the front end, I realised that the combo boxes had lost their lists - the SQL statements that populated them had not been copied properly. I was expecting something like that, so I set about recreating them all as views or stored procedures in SQL, and using them instead.

    One of them refuses to co-operate. In the original Access version, there is a form (frmVCusts) with two combo boxes (cmbSelDepot and cmbSelCusts). [This is because the customer numbers are only unique for a given depot, and it's something that I cannot change.) The recordsources for each were as follows:
    SELECT Null AS Depot
    UNION
    SELECT tblDepots.Depot
    FROM tblDepots
    ORDER BY Depot (Source for cmbSelDepot)

    SELECT tblCustomers.Code
    FROM tblCustomers
    WHERE (((tblCustomer.Depot) = [Forms]![frmVCusts].[cmbSelDepot]))
    ORDER BY tblCustomer.Code

    I created a pair of stored procedures to mimic the above. The second procedure requires an argument, @Depot, and uses this in the WHERE clause to restrict the list of customers to the selected depot. The trouble I'm having (after all that introduction) is, how do I pass the contents of cmbSelDepot to the stored procedure that should be populating cmbSelCust? Is it possible?

    I have tried a work-around involving a parameters table, code on cmbSelDepot_Exit and reworking the stored procedure for cmbSelCust, but then I get an error about the database not being able to convert numeric data to characters...

  2. #2
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    Don't worry - I created a view instead, referencing the system parameters table, and it works!

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I would suggest linking the SQL Server tables into an mdb versus using an ADP. An mdb (with unbound forms) is essentially the same thing as an ADP. But you can develop much faster and easier with an mdb versus an ADP (plus if you use linked SQL Server tables in an mdb, you have all those wonderfully easy to design queries available versus writing all those views and stored procedures.)

    I personally found no advantage at all with ADP versus an mdb (actually, it took three times as long to design an ADP versus an mdb.)
    Last edited by pkstormy; 05-27-09 at 23:58.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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