Thanks Paul - I thought the connection string would be different, I was wondering if access sql and sql server sql syntax was the same.
Here's a small example of what I'm doing:
Code:
Dim cn As ADODB.Connection
Dim rst1 As ADODB.Recordset
Dim rst2 As ADODB.Recordset
Dim rst3 As ADODB.Recordset
Dim rst4 As ADODB.Recordset
Dim rst5 As ADODB.Recordset
Dim rst6 As ADODB.Recordset
Dim rst7 As ADODB.Recordset
Dim rst8 As ADODB.Recordset
Dim rst9 As ADODB.Recordset
Dim rst10 As ADODB.Recordset
Dim rst11 As ADODB.Recordset
Dim rst12 As ADODB.Recordset
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=" & c_Drive & c_Folder & c_SubFolderS & c_DataBaseSecondary & ";"
'******************************************************************************************************************************
'Open Recordset 1 *************************************************************************************************************
Set rst1 = New ADODB.Recordset
rst1.Open c_Second_DB_TBL_1, cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
'Can only add one record (one sample) at a time to the database
With rst1
.AddNew ' create a new record
' add values to each field in the record this is after entries have been validated.
.Fields("Gen_Stock_Code") = Worksheets("INPUTS").OLEObjects("cboGenStockCode").Object.Text
.Fields("Gen_GOJO_Name") = Worksheets("INPUTS").OLEObjects("cboGenGOJOName").Object.Text
'numeric
.Fields("Gen_Numb_INCI_Names") = Worksheets("INPUTS").OLEObjects("cboGenINCINameNumberInput").Object.Text
.Fields("Gen_Function") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_SubFunction") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Preservation") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Source") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Grade_Link") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Irradiation") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Oxidative") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Corrosive") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Numb_Grade") = Worksheets("INPUTS").OLEObjects(" ").Text
'numeric
.Fields("Gen_Numb_Type") = Worksheets("INPUTS").OLEObjects(" ").Text
'numeric
.Fields("Gen_Numb_Certifications") = Worksheets("INPUTS").OLEObjects(" ").Text
'numeric
.Fields("Gen_Numb_Manufacturers") = Worksheets("INPUTS").OLEObjects(" ").Text
'numeric
.Fields("Gen_Numb_Incoming_Tests") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Storage_Shipping") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Flash_Point") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Health") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Flammability") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Reactivity") = Worksheets("INPUTS").OLEObjects(" ").Text
'numeric
.Fields("Gen_Numb_PPE") = Worksheets("INPUTS").OLEObjects(" ").Text
'numeric
.Fields("Gen_Numb_Revisions") = Worksheets("INPUTS").OLEObjects(" ").Text
.Fields("Gen_Spec_Status") = Worksheets("INPUTS").OLEObjects(" ").Text
.Update ' stores the new record
End With
'close the recordset
rst1.Close
Set rst1 = Nothing
...etc