VBA to Access General Code

Hi,

Lets say I have a form in Excel with 2 fields. FirstName and Last Name, when I hit the submit button on the form I need the values from the 2 fields to be updated into an access table. How do I do this?

Regards,
BJoshi

So you are asking for data from a set range in excel to be exported into access into a table?, any table...?

Can you upload a example of a Access Database that your after....?, tables/fields etc..

tar

Any Table, Lets say the access file is called test.mdb, and I need the data to go into Table1 which has the fields Firstname and Lastname.

use ADODB to retrive data from database

Code:

---

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim query As String, Conn_str As String
Conn_str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FilePath"
db.Open Conn_str
query = "SELECT * FROM yourtableName"
rs.Open query, db, adOpenForwardOnly, adLockReadOnly
UserForm1.TextBox1.Value = rs("firstname")
UserForm1.TextBox2.Value = rs("lastname")
rs.Close
Set rs = Nothing
db.Close
Set db = Nothing

---

Code:

---

Sub demo()
    Dim objRS As Object, nwindPath As String
    Set objRS = CreateObject("ADODB.Recordset")
    nwindPath = ThisWorkbook.Path & "\nwind.mdb"
    Dim r As Range
    [a1] = "LastName"
    [b1] = "FirstName"
    [a2] = "Hobson"
    [b2] = "Kenneth"
    Set r = [a1:b2]
    r.Name = "MyRange"
    objRS.Open "INSERT INTO Employees SELECT * FROM [MyRange] IN '" & ThisWorkbook.FullName & "' 'Excel 8.0;'", _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & nwindPath
    Set objRS = Nothing
End Sub

---

Hey Ken, worked like a charm :) Thanks for all your help. I have a question for you, have you ever tried to build/code a finance management system (like an accounting software) in excel or access? How would someone go about doing it?

No, I have not done that. I would just buy a program built to do that.

If your needs are simple, Excel and/or Access might suffice. When doing a complex project like that, ask simple questions here rather the trying to ask many questions at once with too much information overload. That way, you get simple answers quickly. No one will spend time doing a whole project for free typically. You can always add a link to a previous post if background information is pertinent to a new question.