Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection Help
Hi
I am currently using the following code to transfer data into an access database from an excel file. I want the bit in CAPITALS to be declared as a variable so that I can just change the location of the database in the variable. How would I go about changing this? In each of my macros I do not transfer data to the same table in Access. Sub ADOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "DATA SOURCE=C:\FOLDERNAME\DATABASENAME.MDB;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection Help
I have put the following line in 'ThisWorkbook'
Const kDB As String = _ "DATA SOURCE=C:\FOLDERNAME\DATABASENAME.MDB;" and also amended the code in each of the macros that need to connect to the database however, I get an error stating 'Authentication Error'. Any ideas? "Bob Phillips" wrote: Sub ADOFromExcelToAccess() Const kDB As String = _ "DATA SOURCE=C:\FOLDERNAME\DATABASENAME.MDB;" ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & kDB ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- HTH Bob Phillips "Matty G" <Matty wrote in message ... Hi I am currently using the following code to transfer data into an access database from an excel file. I want the bit in CAPITALS to be declared as a variable so that I can just change the location of the database in the variable. How would I go about changing this? In each of my macros I do not transfer data to the same table in Access. Sub ADOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "DATA SOURCE=C:\FOLDERNAME\DATABASENAME.MDB;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
ADO Connection Help
If using in many macros make it global
Public Const kDB As String = _ Sub ADOFromExcelToAccess() "DATA SOURCE=C:\FOLDERNAME\DATABASENAME.MDB;" etc. -- HTH Bob Phillips "Matty G" wrote in message ... I have put the following line in 'ThisWorkbook' Const kDB As String = _ "DATA SOURCE=C:\FOLDERNAME\DATABASENAME.MDB;" and also amended the code in each of the macros that need to connect to the database however, I get an error stating 'Authentication Error'. Any ideas? "Bob Phillips" wrote: Sub ADOFromExcelToAccess() Const kDB As String = _ "DATA SOURCE=C:\FOLDERNAME\DATABASENAME.MDB;" ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & kDB ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub -- HTH Bob Phillips "Matty G" <Matty wrote in message ... Hi I am currently using the following code to transfer data into an access database from an excel file. I want the bit in CAPITALS to be declared as a variable so that I can just change the location of the database in the variable. How would I go about changing this? In each of my macros I do not transfer data to the same table in Access. Sub ADOFromExcelToAccess() ' exports data from the active worksheet to a table in an Access database ' this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long ' connect to the Access database Set cn = New ADODB.Connection cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _ "DATA SOURCE=C:\FOLDERNAME\DATABASENAME.MDB;" ' open a recordset Set rs = New ADODB.Recordset rs.Open "TableName", cn, adOpenKeyset, adLockOptimistic, adCmdTable ' all records in a table r = 3 ' the start row in the worksheet Do While Len(Range("A" & r).Formula) 0 ' repeat until first empty cell in column A With rs .AddNew ' create a new record ' add values to each field in the record .Fields("FieldName1") = Range("A" & r).Value .Fields("FieldName2") = Range("B" & r).Value .Fields("FieldNameN") = Range("C" & r).Value ' add more fields if necessary... .Update ' stores the new record End With r = r + 1 ' next row Loop rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
always recheck data connection library for latest connection strin | Excel Discussion (Misc queries) | |||
no DDE connection?? | Excel Discussion (Misc queries) | |||
Connection using ADO | Excel Programming | |||
SQL Connection | Excel Programming | |||
ODBC connection by udl | Excel Programming |