Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jul 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Using Excel to retrieve and update SQL data (97,SR2)

    I have developed a system which uses a number of dialog boxes to collect various information and produce a report.
    I need to populate some of the edit boxes from SQL. I have experimented with an Access DB (don't have SQL at home!) and had success populating the edit boxes from the DB using an embedded query and using VBA to refresh it.

    Retrieving seems easy, sending the data back has had me running around in circles.

    The final product will be to populate the boxes from SQL 7, manipulate the data and send the data back to the DB.

    I have read volumes and the only thing I learnt was that I know @#$!% all!

    I would appreciate info on where I would find some examples.

    Thanks for your time.

    John

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Excel to retrieve and update SQL data (97,SR2)

    What exactly do you mean by manipulating the data and then sending it back to the DB? Are you changing existing data or adding records? Are you hoping for a "click the button and update the database" kind of solution or something else?

    And who will administer the SQL Server database? Unlike Access, SQL Server takes a lot more care and feeding and you have to understand how it works.
    Charlotte

  3. #3
    New Lounger
    Join Date
    Jul 2001
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Using Excel to retrieve and update SQL data (97,SR2)

    Charlotte,

    Thank you for your time.

    I am retrieving data from an Sql database which populates various list boxes, edit boxes etc in a series of dialog boxes. The data is then used in calculations. The results then need to go back to the DB.

    I will retrieve the same query each time, modify the data and need to update the original DB. I won't be adding new records.

    As for who will administer SQL Server DB? Not me..... The guy that hired me assured me that he would take care of the SQL side.

    I'm an Excel head automating financial systems and have managed to get by all these years without getting too far into the Database world.

    I take your point about care and feeding, over the last week I have learnt that I know nothing and I have a long climb ahead...

    I have not seen the SQL DB yet. The person that is responsible for it assures me that it will be easy.........And your cheque is in the mail. I was experimenting with an Access DB and could populate my dialog boxes with the data, then hit the wall when I wanted to update the DB.

    I AM hoping for a "click the button and update the db" kind of solution or some leads as to where to look for one.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Using Excel to retrieve and update SQL data (97,SR2)

    You will discover that SQL Server is a radically different animal from Access/Jet. I must admit, that I don't know how you would do this in Excel 97 using DAO. It would be fairly easy in Excel 2000 using ADO, though.

    I assume in DAO you would have to use code to create a database object pointing to the SQL Server database, and there is certainly information in the Help files on doing that. Then you can create the update query in code and execute it.

    You will have to allow for the login and password to be passed to the SQL Server database, so this will take working the the DBA for that database to get that part right. Keep in mind though, that if you hard code the login and password into your code, you've just compromised the security of the SQL Server database.
    Charlotte

Posting Permissions

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