Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: MS Access db with 2 user with SQL Server 2003

    Hi,

    I would like to take your advise on below issue.

    I got one client who is having a new supermarket that will open withing next 2-3 months maximum.

    He asked me to make program and I said ok. Using my last experience I can make Access db forms with unbound fields. Before I used MSDE SQL server (personal pc version) as BackEnd and Front End as MS Access 2003 with one single user only. I mean MSDE SQL server and db both were on one pc installed. And there was no problem of speed or anything. My client asked me now new system with the barcode facilities which I can handle because I made one form and its subform successfully using barcode..

    This time he asked for me 2 user system (Excluding Server PC) So I am thinking of Windows Server 2003 (instead of MSDE SQL personal server) at one pc for Back End and Access DB on 2 different PCs. But the main problem is ;

    1. I know only to connect db tbls in FE by linking method (ODBC method that I can do ) but not with codes.

    2. Secondly, it was fine in MS Access 2003 as FE for single user but when it comes to more than one user, definately speed prob will occur.

    3. The third and last very important thing is how can I manage 2 users entering data in same table AT ONE TIME because it will be sales counter with regards to autonumber or serial number of record. Because being a super market sales counter, sales header and sales footer tble will be handled by both user at one time.

    Please I need your guide lines.

    I am stucked here. I can write code behind form to get only last record on the form instead of loading all records from table but my tbls will be linked. Therefore, I want to the system to connect the tbls from server by code and disconnect at closing of forms.

    I dont want a bad start.

    Can someone help me...

    Thanks,
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    first off I'd stick to MSDE if you are used to it, (undxer the covers I think it is a cut down version of SQL server
    Use pass through queries rather than linked tables
    what makes you think it will be significnatly, or even any, slower with two users rather than one.
    unless you have some very weird design or business requirements there should not be a problem with multi user concurrent inserts / access. yess you will have problems if one or more people are running intensive queries / reports on the data whilst rying to insert, or you will have problem if two people are trying to access the same physical row(s) at the same time, but if all you are doing is reading data and inserting rows to a table then I dont' see why you would have a problem
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks healdem,

    I used MSDE Server before (pc edition)

    It makes me worry because I tried this at one hospital where I initially gave them FE & BE on same pc in Access. Later, we placed Windows server 2003 and installed SQL Server 2003 in separate pc as Back End. And I then used linking table method in my Access FE from server (ODBC) which make me crazy. No doubt the SQL server pc was around 10 mtrs away from the receiption. And it was taking very long to display and store data. Hence if I use 2 users then how will be the speed? Hence I came back to FE / BE with Access again.

    Till date I had used only linking (ODBC) table method and not other method to connect tbl from server. Also I never used pass through query. This is the main reason I am worry about.

    If someone can help me just in one header and footer form to conect server with code to modify, edit data then rest I can take care of.

    I need begining steps only.....

    Please help me....
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks healdem,

    The link provides good information. Let me check in practicle and then I will let you know.
    With kind regards,
    Ashfaque

Posting Permissions

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