-
Sep 20th, 2020, 09:32 AM
#1
Thread Starter
New Member
[RESOLVED] Help with Appending Access from Excel
Hi,
I have been creating a customer ordering system for a small local Butchers shop and have been using various tutorials to build on the functionality however I have hit a brick wall, any help would be much appreciated.
Basically the operator builds an invoice based on customer requirement this gets transferred into access for later retrieval if required.
The tutorial I was following showed how to draw the data from access into a form, edit then send back to database, everything works apart from the append function.
The first item in the access database is the autonumber ID
Private Sub cmdAppend_Click()
'Declaring the necessary variables.
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rs As ADODB.Recordset 'dim the ADO recordset class
Dim dbPath As String
Dim i As Integer
Dim x As Integer
'add error handling
On Error GoTo errHandler:
If Me.Arec1.Value = "" Then
MsgBox "You must enter a valid ID number.", _
vbOKOnly Or vbInformation, "Insufficent data"
Exit Sub
End If
'get the path to the database
dbPath = "A:\Database\CustomerData.accdb;"
Set cnn = New ADODB.Connection ' Initialise the collection class variable
'Connection class is equipped with a —method— named Open
'—-4 aguments—- ConnectionString, UserID, Password, Options
'ConnectionString formula—-Key1=Value1;Key2=Value2;Key_n=Value_n;
cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath
Set rs = New ADODB.Recordset 'assign memory to the recordset
'Create the SQL statement to retrieve the data from table.
rs.Open "SELECT * FROM Christmas " & _
"WHERE ID = " & CLng(Me.Arec1), ActiveConnection:=cnn, _
CursorType:=adOpenDynamic, LockType:=adLockOptimistic, _
Options:=adCmdText
If rs.EOF And rs.BOF Then
'Close the recordet and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'Enable the screen.
Application.ScreenUpdating = True
'In case of an empty recordset display an error.
MsgBox "There are no records in the recordset!", vbCritical, "No Records"
Exit Sub
End If
With rs
For i = 2 To 71
rs(Cells(1, i).Value) = Me.Controls("Arec" & i).Value
Next i
rs.Update
End With
'clear the userform values
For x = 1 To 71
Me.Controls("Arec" & x).Value = ""
Next
'Close the recordset and the connection.
rs.Close
cnn.Close
'clear memory
Set rs = Nothing
Set cnn = Nothing
'refresh the listbox
ImportUserForm
'Enable the screen.
Application.ScreenUpdating = True
Me.lstDataAccess.RowSource = "Christmas"
'Inform the user that the macro was executed successfully.
MsgBox "Congratulations the data has been appended", vbInformation, "Append successful"
'error handler
On Error GoTo 0
Exit Sub
errHandler:
'clear memory
Set rs = Nothing
Set cnn = Nothing
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Import_Data"
End Sub
-
Sep 22nd, 2020, 01:45 PM
#2
Re: Help with Appending Access from Excel
It would be helpful if we knew what line was actually causing the error.
Possibly useful link:
https://social.msdn.microsoft.com/Fo...orum=accessdev
-
Sep 23rd, 2020, 01:13 AM
#3
Re: [RESOLVED] Help with Appending Access from Excel
A drive-Letter "A"??? Floppy-Disk?
IIRC i too had problems with something similiar.
What cursor-Type do you use? I remember that i had to switch to server-side cursor to make it work
And i agree with JDC: Which line makes the code go Kaboom?
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|