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
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
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
[VBA]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[/VBA]
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.