Link to home
Start Free TrialLog in
Avatar of Anil Lad
Anil LadFlag for Canada

asked on

Opening table in Access database


I doing a simple connetion to access database.
I open main table1.
then I open table2 in the same database with a sql but in doing so, I am gettig following error

"No Value give for one or more parameters."   Any idea?
Thanks
Anil


Public Sub TransferExcelFile2Database_Click()
 
 
 Set cnhrda = New ADODB.Connection
 cnhrda.CursorLocation = adUseClient
 cnhrda.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.path & "\BE_Tracker.mdb;"

' Open main table
 Dim rsmdb As ADODB.Recordset
 Set rsmdb = New ADODB.Recordset
 rsmdb.Open "Select * from Main_Records", cnhrda, adOpenStatic, adLockOptimistic
 
   
 rs.MoveFirst
   
    While Not rs.EOF
        With rsmdb
        .AddNew
        ' Staus is Issue/Open/Cancelled

'2)  read second table        
    Dim sSql As String
    Dim rsTemp As ADODB.Recordset
    Set rsTemp = New ADODB.Recordset
   
    sSql = "Select Staus_ID from LKP_Status where Status_name=""" & rs!Status & """"
   
     
     rsTemp.Open sSql, cnhrda, adOpenStatic, adLockReadOnly
'                ^---I get error here.   "No Value give for one or more parameters."   Any idea?

    If Not rsTemp.EOF Then
        !Status = rsTemp!Status_ID
    End If
           
      '  !Status = getStatus(rs!Status)
       
        !Name = rs!Name
        !address = rs!address
        !age = rs!age
        .Update
        End With
      rs.MoveNext
    Wend
   
    rsmdb.Close
    MsgBox "completed"
   
   
End Sub    
Avatar of Anil Lad
Anil Lad
Flag of Canada image

ASKER


Here's the complete code





Dim ws As Workspace
Dim db As Database
Dim strConnection As String
'Set ws = DBEngine.Workspaces(0)

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim file, path As String

Public cnhrda As ADODB.Connection


Private Sub OpenDatabase_Click()
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
         "Data Source='" & Text1.Text & "';" & _
         "Extended Properties=""Excel 8.0;HDR=yes"""
                    'HDR = yes - to exclude header of excel file upon reading
Set rs = New ADODB.Recordset
rs.Open "Select * from [OTTAWA$]", con, adOpenStatic, adLockOptimistic

MsgBox "Destination Database has been open"
End Sub

Private Sub cmdGetExcel_Click()
On Error GoTo errortrap
With CommonDialog1
.DialogTitle = "Open Image File..."
.Filter = "Image files(*.xls)|*.xls"
.CancelError = True
procReOpen:
.ShowSave
End With
path = CommonDialog1.FileName
Text1.Text = path

Exit Sub
errortrap: MsgBox Err.Description, vbExclamation, "error"
End Sub


Public Sub TransferExcelFile2Database_Click()
 
 
 Set cnhrda = New ADODB.Connection
 cnhrda.CursorLocation = adUseClient
 cnhrda.Open "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=" & App.path & "\BE_Tracker.mdb;"


 Dim rsmdb As ADODB.Recordset
 Set rsmdb = New ADODB.Recordset
 rsmdb.Open "Select * from Main_Records", cnhrda, adOpenStatic, adLockOptimistic
   
 '--
'  Dim rsTemp As ADODB.Recordset
'  Set rsTemp = New ADODB.Recordset
' sSql = "Select Staus_ID from LKP_Status where Status_name=Issued"
' rsTemp.Open "Select * from Main_Records", cnhrda, adOpenStatic, adLockReadOnly

 '--
 
   
 rs.MoveFirst
   
    While Not rs.EOF
        With rsmdb
        .AddNew
        ' Staus is Issue/Open/Cancelled
       
    Dim sSql As String

    Dim rsTemp As ADODB.Recordset
    Set rsTemp = New ADODB.Recordset
   
    sSql = "Select Staus_ID from LKP_Status where Status_name=""" & rs!Status & """"
   
 
     rsTemp.Open sSql, cnhrda, adOpenStatic, adLockReadOnly
     
     
     
     
     
   
    If Not rsTemp.EOF Then
        !Status = rsTemp!Status_ID
    End If
           
      '  !Status = getStatus(rs!Status)
       
        !Name = rs!Name
        !address = rs!address
        !age = rs!age
        .Update
        End With
      rs.MoveNext
    Wend
   
    rsmdb.Close
    MsgBox "completed"
   
   
End Sub
Public Function getStatus(ByVal inStatusName As String) As String
    'Step 1.  Read the staus table
    '         Find the matching  status name and corresponding status id
    '         Pass the status id
    'Status_ID
    'Status_Name
    Dim sSql As String

    Dim rsTemp As ADODB.Recordset
    Set rsTemp = New ADODB.Recordset
   
    sSql = "Select Staus_ID from LKP_Status where Status_name=""" & inStatusName & """"
   
   '  rsTemp.Open "Select * from Main_Records", cnhrda, adOpenStatic, adLockReadOnly
   
   
     rsTemp.Open sSql, cnhrda, adOpenStatic, adLockReadOnly
   
    If Not rsTemp.EOF Then
        getStatus = rsTemp!Status_ID
    End If
   
   
    On Error GoTo NetworkError
   
    Exit Function
   
NetworkError:
    Select Case Err.Number
    Case 53
        MsgBox "Unable to find NWNETAPI.DLL"
        getuserid = "NoNetwork"
   
    Case Else
        MsgBox "Unknown Error: " & Err.Number & " " & Err.Description
        getuserid = ""
    End Select
   
End Function
ASKER CERTIFIED SOLUTION
Avatar of leonstryker
leonstryker
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks  leonsryker.
 I was too anxious.  I took the sql and ran it in access and found that  Staus_ID field did not exist.
I then noted that I had misspelled the field name it should have been Status_ID  -- the t was missing.
......irrrrrrrk.  those small errors.  I think I did it with your name too!..

Thanks.
Anil