Current record does not support updating...
-
I have Error Message #3251 that occurs when I try to update one of the fields from one table based upon the value stored in another table. I am trying to compare the values of several fields in each of two tables, and when there is a match, I would like to assign the value of the second table to the value of the first table. When all records have had assignments, then I would perform a link between the first table and another table in the database, and then export the results with a portion of the data from each of the two tables in a query. Seems easier than trying to match a composite key from one table against a composite key from the other table. Besides, I don't think it is possible to compare composite keys. It seems better to merely compare indexes. Why doesn't it permit me to update the first table based upon results from the second table? I believe it has something to do with the Lock Type because the error message states: "Error and Error #3251: ADODB.Recordset: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype." I need some assistance here. Here is the code so far:
Private Sub ProcessHeadcountRecords()
Dim dbsHeadcount As Database
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstInputFile As ADODB.Recordset
Dim cmdSQLInputFile As ADODB.Command
Dim strSQLInputFile As String
Dim rstHyperionMany As ADODB.Recordset
Dim cmdSQLHyperionMany As ADODB.Command
Dim strSQLHyperionMany As String
Dim rstHyperionOne As ADODB.Recordset
Dim cmdSQLHyperionOne As ADODB.Command
Dim strSQLHyperionOne As String
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String
Set dbsHeadcount = CurrentDbSet cmdSQLInputFile = New ADODB.Command Set cmdSQLInputFile.ActiveConnection = Application.CurrentProject.Connection strSQLInputFile = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_ "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[09/12/2007 Reported\], " & \_ "\[NUMINDEX\] FROM \[TBLINPUTFILE\]" cmdSQLInputFile.CommandType = adCmdText cmdSQLInputFile.CommandText = strSQLInputFile Set rstInputFile = cmdSQLInputFile.Execute() rstInputFile.MoveFirst Set cmdSQLHyperionMany = New ADODB.Command Set cmdSQLHyperionMany.ActiveConnection = Application.CurrentProject.Connection strSQLHyperionMany = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_ "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[NU -
I have Error Message #3251 that occurs when I try to update one of the fields from one table based upon the value stored in another table. I am trying to compare the values of several fields in each of two tables, and when there is a match, I would like to assign the value of the second table to the value of the first table. When all records have had assignments, then I would perform a link between the first table and another table in the database, and then export the results with a portion of the data from each of the two tables in a query. Seems easier than trying to match a composite key from one table against a composite key from the other table. Besides, I don't think it is possible to compare composite keys. It seems better to merely compare indexes. Why doesn't it permit me to update the first table based upon results from the second table? I believe it has something to do with the Lock Type because the error message states: "Error and Error #3251: ADODB.Recordset: Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype." I need some assistance here. Here is the code so far:
Private Sub ProcessHeadcountRecords()
Dim dbsHeadcount As Database
Dim Cnxn As ADODB.Connection
Dim strConn As String
Dim rstInputFile As ADODB.Recordset
Dim cmdSQLInputFile As ADODB.Command
Dim strSQLInputFile As String
Dim rstHyperionMany As ADODB.Recordset
Dim cmdSQLHyperionMany As ADODB.Command
Dim strSQLHyperionMany As String
Dim rstHyperionOne As ADODB.Recordset
Dim cmdSQLHyperionOne As ADODB.Command
Dim strSQLHyperionOne As String
Dim strDBPath As String
Dim strFileName As String
Dim strMessage As String
Set dbsHeadcount = CurrentDbSet cmdSQLInputFile = New ADODB.Command Set cmdSQLInputFile.ActiveConnection = Application.CurrentProject.Connection strSQLInputFile = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_ "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[09/12/2007 Reported\], " & \_ "\[NUMINDEX\] FROM \[TBLINPUTFILE\]" cmdSQLInputFile.CommandType = adCmdText cmdSQLInputFile.CommandText = strSQLInputFile Set rstInputFile = cmdSQLInputFile.Execute() rstInputFile.MoveFirst Set cmdSQLHyperionMany = New ADODB.Command Set cmdSQLHyperionMany.ActiveConnection = Application.CurrentProject.Connection strSQLHyperionMany = "SELECT \[COUNTRY\], \[TYPE\], \[BUSINESS UNIT\], " & \_ "\[L/R/G\], \[REGION\], \[JOB FUNCTION\], \[NUnew_phoenix wrote:
Set rstHyperionMany = cmdSQLHyperionMany.Execute()
The Execute method returns a read-only recordset. You should use the Recordset.Open() method and specify an appropriate CursorType e.g. adOpenKeyset to make your recordset updateable.
Paul Marfleet