Results 1 to 3 of 3

Thread: Parameter @Last_name has no default value

  1. #1

    Thread Starter
    Registered User
    Join Date
    Oct 2017
    Posts
    1

    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

  2. #2
    gibra
    Guest

    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.

  3. #3
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,618

    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
  •  



Click Here to Expand Forum to Full Width