Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    May 2003
    Location
    tunbridge wells, Kent, England
    Posts
    36
    Thanks
    0
    Thanked 0 Times in 0 Posts

    InputBox (Excel/Access 2000)

    Hi

    Whilst working in an Excel spreadsheet, I need to prompt the user to enter text into an InputBox, then paste that value into the "PartnerDb" field of an Access table called "PersonalDatabase". So far I've managed to get the InputBox up and running but can't yet do anything with the result! This is it so far:

    PDb = InputBox("Which Partner or Consultant?") I then need to do something like the following:

    PDb = "INSERT INTO PersonalDatabase ( PartnerDb)"

    This will be need to be a macro. Please could someone supply the missing bits for me!

    Many thanks.

    Sue

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: InputBox (Excel/Access 2000)

    You will have to use ADO or DAO to connect to the database. Here is code using ADO; I used late binding so that you don't have to set a reference in the Excel workbook. Substitute the name and path of the database you are using. I assumed that you are inserting a text value.<pre>Dim Pdb As String
    Dim cnn As Object

    Pdb = InputBox("Which Partner or Consultant?")
    If Pdb = "" Then Exit Sub

    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:AccessTest.mdb"
    cnn.Execute "INSERT INTO PersonalDatabase ( PartnerDb ) VALUES ( " &
    Chr(34) & Pdb & Chr(34) & ")"
    cnn.Close
    Set cnn = Nothing</pre>

    HTH

Posting Permissions

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