-
Oct 8th, 2017, 08:38 AM
#1
Thread Starter
Registered User
Parameter @Last_name has no default value
Hi all,
I have problem developing this macro. I have to export data from excel to Accces, I get this error: Parameter @Last_name has no default value
Does anyone know why?
Code:
Public Sub AggiungiDB()
Dim cn, cmd As Object
Dim lng, FirstRow, Answer As Long
Dim dbMTPath As String
Dim sh As Worksheet
dbPath = "C:\First Step Database (new)_be.accdb"
Answer = MsgBox("Insert new entry in the database?", vbYesNo + vbQuestion, "Warning.")
If Answer = vbYes Then
Set sh = ThisWorkbook.Worksheets("Demographics for export")
With sh
FirstRow = .Range("A" & .Rows.Count).End(xlUp).Row
If sh.Range("a3") = 0 Then
MsgBox "No Data to export"
Exit Sub
End If
End With
Set cn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")
With cn
.CursorLocation = 1
.Open "Provider=Microsoft.ACE.OLEDB.12.0; " _
& "Data Source=" & dbPath '& ThisWorkbook.Path & _
End With
Set cmd.ActiveConnection = cn
With cmd
.CommandText = "INSERT INTO [Main table]([Last name], [First name], DOB, [Country of Birth], [Indigenous status], Gender, [Dependent children], [Drug of concern], [Address line 1], Arrested_ever, Out_of_home_care_ever, Sexual_abuse_ever, Self_harm_ever, Suicide_ever, Ever_been_incarcerated, Ever_been_on_a_CCO) VALUES (@Last_name, @First_name, @DOB, @Country_of_Birth, @Indigenous_status, @Gender, @Dependent_children, @Drug_of_concern, @Address_line_1, @Arrested_ever, @Out_of_home_care_ever, @Sexual_abuse_ever, @Self_harm_ever, @Suicide_ever, @Ever_been_incarcerated, @Ever_been_on_a_CCO)"
.CommandType = 1
For lng = 3 To FirstRow
.Parameters("@Last_name").Value = sh.Range("A" & lng).Value
.Parameters("@First_name").Value = sh.Range("B" & lng).Value
.Parameters("@DOB").Value = sh.Range("C" & lng).Value
.Parameters("@Country_of_Birth").Value = sh.Range("D" & lng).Value
.Parameters("@Indigenous_status").Value = sh.Range("E" & lng).Value
.Parameters("@Gender").Value = sh.Range("F" & lng).Value
.Parameters("@Dependent_children").Value = sh.Range("G" & lng).Value
.Parameters("@Drug_of_concern").Value = sh.Range("H" & lng).Value
.Parameters("@Address_line_1").Value = sh.Range("I" & lng).Value
.Parameters("@Arrested_ever").Value = sh.Range("J" & lng).Value
.Parameters("@Out_of_home_care_ever").Value = sh.Range("K" & lng).Value
.Parameters("@Sexual_abuse_ever").Value = sh.Range("L" & lng).Value
.Parameters("@Self_harm_ever").Value = sh.Range("M" & lng).Value
.Parameters("@Suicide_ever").Value = sh.Range("N" & lng).Value
.Parameters("@Ever_been_incarcerated").Value = sh.Range("O" & lng).Value
.Parameters("@Ever_been_on_a_CCO").Value = sh.Range("P" & lng).Value
.Execute
Next
End With
End If
RigaChiusura:
If Not cn Is Nothing Then
If cn.State = 1 Then
cn.Close
End If
End If
Set cmd = Nothing
Set cn = Nothing
sh.Range("A3:P" & FirstRow).Value = ""
Set sh = Nothing
Exit Sub
RigaErrore:
MsgBox Err.Number & vbNewLine & Err.Description
Resume RigaChiusura
End Sub
-
Oct 8th, 2017, 02:12 PM
#2
Re: Parameter @Last_name has no default value
First, You need to declare a Parameter object (before to use it)
Next, you need to ADD the Parameter to Parameters collection
N.B.
Declaration like this:
Code:
Dim cn, cmd As Object
Isn't correct, because in this case cn was declared as Variant, NOT as Object as you expect.
Each variable must be declared AS TYPE.
-
Oct 8th, 2017, 03:24 PM
#3
Re: Parameter @Last_name has no default value
Code:
For i = 0 To UBound(P): Cmd.Parameters.Append Cmd.CreateParameter(, VarType(P(i)), , , P(i)): Next
copied from some other forum post by olaf
the above demonstrates adding parameters from a parameter array
you should change the vartype to appropriate for your database field and the p(i) to the cell value,
else create an array of values from the range and loop through the array (as above), though the array elements may not be typed to match the database fields
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
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
|