PDA

View Full Version : VBA to Access General Code



bjoshi
08-09-2011, 01:24 AM
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

ads_3131
08-09-2011, 01:38 AM
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

bjoshi
08-09-2011, 01:53 AM
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.

mohanvijay
08-09-2011, 07:56 PM
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

Kenneth Hobs
08-09-2011, 08:40 PM
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

bjoshi
08-11-2011, 09:58 AM
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?

Kenneth Hobs
08-11-2011, 10:05 AM
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.