Anil Lad
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
Set cnhrda = New ADODB.Connection
cnhrda.CursorLocation = adUseClient
cnhrda.Open "Provider=Microsoft.Jet.OL
' 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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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.OL
"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
Set cnhrda = New ADODB.Connection
cnhrda.CursorLocation = adUseClient
cnhrda.Open "Provider=Microsoft.Jet.OL
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