Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003
    Location
    bay area
    Posts
    2

    Unanswered: sql server db access from excel

    Hello. I'm trying to do something that I think should be fairly simple but can't seem to get the right info to get started. I have sql server 2000 running on server 2000. I'm trying to write a VB macro for ExcelXP that will get data from a sql server db and display it in the worksheet. Also, I want to be able to write back changes made in Excel to the sql server db. If anyone can point me to a good (basic) resource or give me some code to start with I would apprciate it very much. I have limited experience with VB but I've done a lot of php/mysql programming so I think I can pick it up. Thanks.

  2. #2
    Join Date
    Dec 2003
    Posts
    17
    To import the data from SQL Server you have to go to the Data menu option => Import External Data and create a connection to the SQL Server that will return the data you require. To copy this process start recording a macro and you will be able to play with that later.

    To update data in SQL Server from Excel you will need to create a reference (Excel Microsoft Visual Basic Editor) by going to the Tools menu option => References and setting a reference to something like "Microsoft ActiveX Data Objects 2.7 Library" (it is possible to download this from the microsoft site, it will include some help files with it). You can then make an ADODB Command that will use SQL to update the SQL Server tables. You must determine which records have been changed.

    I hope that helps a bit or at least gives you an idea where to start looking.

  3. #3
    Join Date
    Dec 2003
    Location
    bay area
    Posts
    2

    thanks

    Thanks for your reply. I decided to do this a different way but you response was helpful.

Posting Permissions

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