Afternoon all,
I am new to this forum so please forgive me if I have posted in the wrong section,
I am trying to use Excel VB to write to an access database which is working brilliantly sometimes...
In Excel on Sheet 1 I have the data to be written to the access database,
In Excel on Sheet 2 I have created a data connection that refreshes the full table (that I am writing to from Sheet 1 to the access database)
The below codes works perfectly until I refresh the data connection on sheet 2, then when I next try and run the code below I get the following error:
Run-time error ‘3251’
Current Recordset does not support updating. This may be a limitation of the provider, or the selected locktype
I have looked up and down the web to no avail, the only way I can get around it is to close the Excel workbook and re-open it without refreshing the data connection and then trying again and it works.
If anybody has any idea I would be most appreciative!
Here is the code:
Sub SingleLineTransfer()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
On Error GoTo Errhandler
Dim cn As ADODB.Connection, rs As ADODB.Recordset
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jamie\Desktop\Group Commercial System\Group Commercial System Database.accdb;Persist Security Info=False;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "DB_Main", cn, adOpenKeyset, adLockPessimistic, adCmdTable
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Stage and Gate Ref") = Range("A" & 2).Value
.Fields("Project Name") = Range("B" & 2).Value
.Fields("# Of Products") = Range("C" & 2).Value
.Fields("Company Submission") = Range("D" & 2).Value
.Fields("Submitted By") = Range("E" & 2).Value
.Fields("Workstation Name") = Range("F" & 2).Value
.Fields("Domain") = Range("G" & 2).Value
.Fields("Submission Time and Date") = Range("H" & 2).Value
.Fields("Project Leader") = Range("I" & 2).Value
.Fields("Req Output Currency") = Range("M" & 2).Value
' add more fields if necessary...
.Update ' stores the new record
End With
' rs.Close
'Set rs = Nothing
' cn.Close
' Set cn = Nothing
GoTo Finish
Errhandler:
If Err.Number = 3251 Then GoTo ConnectionError
If Err.Number = -2147217887 Then GoTo DuplicateError
ConnectionError:
MsgBox "Connection Error, Please Contact Your System Administrator", vbCritical, "System Error"
GoTo Finish
DuplicateError:
MsgBox "That Stage and Gate Reference Is Already Being Utilised, Please Try Again", vbCritical, "System Restriction"
Finish:
End Sub
Thanks in advance guys!
I am new to this forum so please forgive me if I have posted in the wrong section,
I am trying to use Excel VB to write to an access database which is working brilliantly sometimes...
In Excel on Sheet 1 I have the data to be written to the access database,
In Excel on Sheet 2 I have created a data connection that refreshes the full table (that I am writing to from Sheet 1 to the access database)
The below codes works perfectly until I refresh the data connection on sheet 2, then when I next try and run the code below I get the following error:
Run-time error ‘3251’
Current Recordset does not support updating. This may be a limitation of the provider, or the selected locktype
I have looked up and down the web to no avail, the only way I can get around it is to close the Excel workbook and re-open it without refreshing the data connection and then trying again and it works.
If anybody has any idea I would be most appreciative!
Here is the code:
Sub SingleLineTransfer()
' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
On Error GoTo Errhandler
Dim cn As ADODB.Connection, rs As ADODB.Recordset
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Jamie\Desktop\Group Commercial System\Group Commercial System Database.accdb;Persist Security Info=False;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "DB_Main", cn, adOpenKeyset, adLockPessimistic, adCmdTable
With rs
.AddNew ' create a new record
' add values to each field in the record
.Fields("Stage and Gate Ref") = Range("A" & 2).Value
.Fields("Project Name") = Range("B" & 2).Value
.Fields("# Of Products") = Range("C" & 2).Value
.Fields("Company Submission") = Range("D" & 2).Value
.Fields("Submitted By") = Range("E" & 2).Value
.Fields("Workstation Name") = Range("F" & 2).Value
.Fields("Domain") = Range("G" & 2).Value
.Fields("Submission Time and Date") = Range("H" & 2).Value
.Fields("Project Leader") = Range("I" & 2).Value
.Fields("Req Output Currency") = Range("M" & 2).Value
' add more fields if necessary...
.Update ' stores the new record
End With
' rs.Close
'Set rs = Nothing
' cn.Close
' Set cn = Nothing
GoTo Finish
Errhandler:
If Err.Number = 3251 Then GoTo ConnectionError
If Err.Number = -2147217887 Then GoTo DuplicateError
ConnectionError:
MsgBox "Connection Error, Please Contact Your System Administrator", vbCritical, "System Error"
GoTo Finish
DuplicateError:
MsgBox "That Stage and Gate Reference Is Already Being Utilised, Please Try Again", vbCritical, "System Restriction"
Finish:
End Sub
Thanks in advance guys!
