Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    copy from form in excel (2003 sp2)

    I have a Form in Excel that when the 'Execute' button is pressed, it collects (populates to appropriate label) 13 different values from the activeworkbook. It just so happens that these 13 values are what i need to create a new record in my Access db. How would I go about getting these values into the db from the Excel form?

    thanks,
    jackal

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

    Re: copy from form in excel (2003 sp2)

    You can use DAO or ADO to append a record to a table in an Access database. For example, with a reference to the Microsoft DAO 3.6 Object Library set (in Tools | References...):

    Dim dbs As DAO.Database
    Dim rst As DAO.RecordSet
    Set dbs = DBEngine.OpenDatabase("CatabasesMyDatabase.mdb")
    Set rst = dbs.OpenRecordset("tblSomething", dbOpenDynaset)
    rst.AddNew
    rst!ThisField = Me.Label1.Caption
    rst!ThatField = Me.TextBox2.Text
    rst![Other Field] = Me.ComboBox3.Text
    rst.Update
    rst.Close
    Set rst = Nothing
    dbs.Close
    Set dbs = Nothing

    Added Note that this code is supposed to be run from the userform in Excel, for example in the Click event of a command button.

  3. #3
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy from form in excel (2003 sp2)

    Thanks Hans!!

    One question about db location. My db is located on a network drive.
    Here is the line of code I have used and it says it is unavailable. Am I typing the location wrong?
    <pre>Set dbs = DBEngine.OpenDatabase("Pe2580AutocadExcavationexca vation.mdb")</pre>


    thanks,
    jackal

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

    Re: copy from form in excel (2003 sp2)

    As far as I know, OpenDatabase should be able to use a UNC path. Is the database password-protected?

  5. #5
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy from form in excel (2003 sp2)

    db is NOT password protected.

    thanks,
    jackal

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

    Re: copy from form in excel (2003 sp2)

    Hmm, not sure then. Does it work if you use a drive letter instead of a UNC path?

  7. #7
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy from form in excel (2003 sp2)

    Yes it does work with the drive letter. I need to find a way to make this work with a UNC path, the computer that will be running this code does not have any mapped drives.

    thanks,
    jackal

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

    Re: copy from form in excel (2003 sp2)

    Let's try ADO instead of DAO. Clear the reference to the Microsoft DAO 3.6 Object Library and set one to the Microsoft ActiveX Data Objects 2.n Library instead (pick an n that is available in the list).

    Code:

    Dim cnn As New ADODB.Connection
    Dim rst As New ADODB.Recordset
    cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Pe2580AutocadExcavationexcavation.mdb"
    rst.Open "tblSomething", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
    rst.AddNew
    rst!ThisField = Me.Label1.Caption
    rst!ThatField = Me.TextBox2.Text
    rst![Other Field] = Me.ComboBox3.Text
    rst.Update
    rst.Close
    Set rst = Nothing
    cnn.Close
    Set cnn = Nothing

  9. #9
    2 Star Lounger
    Join Date
    Jan 2004
    Posts
    196
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: copy from form in excel (2003 sp2)

    I am notorious for the "type-O". The server name is Pe2850 not Pe2580.

    thanks,
    jackal

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

    Re: copy from form in excel (2003 sp2)

    OK, glad you found it.

Posting Permissions

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