Export data from VBA Userform to Access?

[mmartinez13]

How do I export data from a VBA Userform to Access? I have 7 columns and 8 rows, I would like to have these exported to Access in order from my userform.

Thanks in advance for all the help.

 

[JP2112]

I'm confused, do you have this information on a worksheet? Or you have a userform that's made to look like a worksheet?

 

[mmartinez13]

JP:

I have created a userform that looks like a worksheet.

Thanks.

 

[JP2112]

Sorry to hear that. Do you already have a table in Access, or do you need to create a new one?

 

[mmartinez13]

I do not have a table in Access yet, so, I will need to create a new one.

I do have a worksheet also. I was wanting to use the userform for operator use. I can use the worksheet if needed.

 

[JP2112]

Without more information about the structure of the userform and the table that is needed, it will be difficult to write the appropriate code. Do you already have code that collects user input from the userform and puts it into variables?

If the table only needed to be created once, I would just create it manually in Access. Otherwise I would use ADO to execute a SQL statement against the DB to create the table as often as needed.

Either way, I would use ADO to open the database and insert the data from the userform into the fields of the table.

This procedure could be used to create the table (assumes Access 2003):

Sub ExecuteQuery(databasePath As String, sqlQuery As String)
Dim conn As Object ' ADODB.Connection
 
  Set conn = CreateObject("ADODB.Connection")
  conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=" & databasePath & ";"
  conn.Execute sqlQuery
End Sub

Source: http://www.vb-helper.com/howto_ado_create_table.html

The same procedure could be used to populate the table with information from your userform.

 

[mmartinez13]

JP:

Thank you very much. I will check it out.