Vba Button Export Data From Xls To Mdb

I am working on a project that will be a test for users. The forum they will get will have 4 worksheets two of which will be hidden. The first one will be a general title page asking for their name. The second one will give them a series of question and they will select the answers from a drop down menu. Now their answers will be parsed with the answers using an if statement on the last sheet. From there the last sheet will have their name which is pulled from the first forum and their results for the questions if an X is put then they got it right if its blank they got it wrong.

The main thing I am wanting to do is when the user finishes inputing their answers there will be a button that says submit which will use a VBA script to send the data from sheet 4 with their name and the X or no X data to a access database on the network. From there a master sheet will read the data so results can be viewed.

My access is a little rusty along with my VBA I do apologize and I am looking for a solution to this on my own but I would like to seek advise from others that may have more knowledge or more brushed up on these areas.

Thank you to anyone in advanced.

Re: Vba Button Export Data From Xls To Mdb

Quote from gollem

You should use ADO for this.
The example below shows how to add data to a database using ADO.

Don't forget to set your reference, otherwise the code won't work.

Code

'Don't forget to set a reference: tools-reference-Microsoft activex dataobjects 2.1
    'or some other version
    
    'Open access-database connection
    Dim cnn As ADODB.Connection
    Set cnn = New ADODB.Connection
    cnn.Provider = "Microsoft.jet.oledb.4.0"
    cnn.ConnectionString = "c:\tmp\db.mdb"
    cnn.Open
    
    'Open recordset
    Dim rstData     As ADODB.Recordset
    Set rstData = New ADODB.Recordset
    
    'Open table tblTable from your database
    rstData.Open "SELECT * from tblTable", cnn, adOpenDynamic, adLockOptimistic
    rstData.AddNew
    rstData.Fields!Field1 = "test1" 'Use your own fieldname instead of field1
    rstData.Fields!Field2 = "test2"
    rstData.Update
    
    'Close connections
    rstData.Close
    Set rstData = Nothing
    cnn.Close
    Set cnn = Nothing

Display More

Hope this gives you an idea.

Display More

Thanks that is a different approach to what I came up with but I might test it out to see if it works better than the current one I have ...

Sub Macro2()
' Macro2 Macro
Dim Field As String
    Set oAPP = CreateObject("Access.Application")
    oAPP.AutomationSecurity = 1 ' set macro security LOW.
    oAPP.OpenCurrentDatabase "H:\pathtodatabase\results.mdb", False, "Password"
    oAPP.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "TableName, Application.ThisWorkbook.Path & "\Workbook.xls", True, "Sheet3$A1:G2"
    oAPP.Visible = False
    oAPP.UserControl = False
    Application.StatusBar = "Now Submitting Your Answers..."
    Application.ScreenUpdating = True
    Dim usern
    usern = Worksheets("Sheet3").Range("A2")
    If usern = "" Then
    MsgBox "Your answers have already been submitted " & usern & " please close this program.", , "Answers Already Submitted"
    Else
    MsgBox "Your answers have been submitted " & usern & " thank you!", , "Answers Submitted"
    End If
    Application.StatusBar = ""
    oAPP.CloseCurrentDatabase
    oAPP.Quit
    Set oAPP = Nothing
End Sub