SQL/Access Question

Discussion in 'OT Technology' started by XtremelyCanadian, Feb 24, 2004.

  1. XtremelyCanadian

    XtremelyCanadian I play hockey.....EH

    Joined:
    Jan 4, 2002
    Messages:
    1,801
    Likes Received:
    0
    Location:
    Edmonton,Canada
    I did a small program for a company in visual basic and SQL Server to track outstanding service work. Im just finishing it up and have figured out that SQL Server is too big a database for what the company needs and want to transfer over to Access. Ive only used Access minimally and want to know if there is a way to transfer over my existing database with all the values to Access ?? :dunno:
     
  2. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    use DTS to Export the data. Right click you database in Enterprise Manager, and it will ask you for the source (your sql server database) and then the destination (MS Access) and it will take you through the necessary steps. Why is the database too "big"? cost wise? If so, look into MSDE 2000. It is a free "watered down" version of MS SQL Server 2000.
     
  3. XtremelyCanadian

    XtremelyCanadian I play hockey.....EH

    Joined:
    Jan 4, 2002
    Messages:
    1,801
    Likes Received:
    0
    Location:
    Edmonton,Canada
    Not too big cost wise, its just only one person at a time will be using the database and they currently have really old computers. Its just really slow and annoying to use on there computers, so i thought i would learn some Access while im at it.
     
  4. XtremelyCanadian

    XtremelyCanadian I play hockey.....EH

    Joined:
    Jan 4, 2002
    Messages:
    1,801
    Likes Received:
    0
    Location:
    Edmonton,Canada
    I just transfered it over to Access and it worked. The only problem is i dont think it transfered all of my procedures over.....can i transfer them over too or do i have to re-insert them ????
     
  5. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    procedures? i'm assuming you mean stored procedures? Access doesn't have those. They have stored queries, but i'm not sure if that is what you want. They are basically stored select/update/delete statements, but cannot do any processing of sql code other than that.
     
  6. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Yeah, you have rewrite them into VBA code. You can use user functions within queries however. An example would be a function that transformed $1234.67 into

    ****** One Thousand Two Hundred and Thrity-Four Dollars and Sixty-Seven Cents ******

    as a string (to write cheques...)
     
  7. XtremelyCanadian

    XtremelyCanadian I play hockey.....EH

    Joined:
    Jan 4, 2002
    Messages:
    1,801
    Likes Received:
    0
    Location:
    Edmonton,Canada
    So Access wouldnt be able to do a procedure such as

    If it cant, what would be a watered-down database that i could transfer over from SQL Server 7.0 ????
     
  8. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Sure it can do that, but you have to translate it into VBA that's all. Now if you have a lot of stored procedures, that would suck. If it's just this one, then it's not that big of a deal. I mean it looks like all you're doing is adding a record here and ensuring the that specific fields are filled in. You can set up your table to not accept null values and when you do an "append query", it will fail at adding the record.

    There is no easy way to transfer stored procedures into Access. However, it wouldn't take long to convert a handful of procedures over, and you may find that some of the built in error handling (like null fields = :nono: ) may handle a lot of your "error checking" for you.
     
  9. XtremelyCanadian

    XtremelyCanadian I play hockey.....EH

    Joined:
    Jan 4, 2002
    Messages:
    1,801
    Likes Received:
    0
    Location:
    Edmonton,Canada
    That is like one of about 30 other procedures that do anything from add/update or delete, i would not want to redo them all in Access. Any other databases that i could use that are similar in performance to Access but i can still use my SQL code in ????
     
  10. 5Gen_Prelude

    5Gen_Prelude There might not be an "I" in the word "Team", but

    Joined:
    Mar 14, 2000
    Messages:
    14,519
    Likes Received:
    1
    Location:
    Vancouver, BC, CANADA
    Not that I know of. But SLED makes a good point - you can do a lot more with simple queries in access than you can in standard SQL. That one procedure looks like it could all be done in a simple access query. Probably take me a couple of hours to do it, depending on where the data comes from and how many fields are involved.
     
  11. SLED

    SLED build an idiot proof device and someone else will

    Joined:
    Sep 20, 2001
    Messages:
    28,118
    Likes Received:
    0
    Location:
    AZ, like a bauce!
    what is your client app developed in? I've had MSDE installations on some pretty slow ass systems, and had little to no trouble with speed. Tell you client to quit being cheap, and to buy a $200-300 computer for once!
     
  12. XtremelyCanadian

    XtremelyCanadian I play hockey.....EH

    Joined:
    Jan 4, 2002
    Messages:
    1,801
    Likes Received:
    0
    Location:
    Edmonton,Canada
    Well i would tell him to buy a computer, but this programs database will not get too large. Maybe something like mySQL would be a bit more efficient :dunno:
     

Share This Page