Basic Code... Please Help

[VB6AccessSQL]

Hi All,

is there someone who could help me as in basically.
codings for file maintenance (ADD,EDIT,DELETE,SAVE,SEARCH) using VB6 with Access and SQL format?

can i code each per command button?

Please check my below code;i have an error in {Run-time error '3265' item cannot be found in the collection corresponding to the requested name or ordinal

Option Explicit
Dim oCN As ADODB.Connection
Dim oRS As ADODB.Recordset
Dim cmd As ADODB.Command


Private Sub Command1_Click()
Dim catNewDB As ADOX.Catalog
Dim sDBPath As String
Dim sCreateDBString As String
Dim meSQL As String
Dim sConnectString As String
Dim mymsg As String



If Dir("C:\My Lesson", vbDirectory) <> "" Then
Else
Call MkDir("C:\My Lesson")
End If

sDBPath = "C:\My Lesson\mydb.MDB"

If Dir(sDBPath, vbNormal) <> "" Then
Kill sDBPath
End If

sCreateDBString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath & _
";Jet OLEDB:Engine Type=5;"

Set catNewDB = New ADOX.Catalog
catNewDB.Create sCreateDBString
Set catNewDB = Nothing

Set oCN = New ADODB.Connection
'Set oCN = Nothing

sConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBPath

'gani to structure
'#1 open connection
'#2 ur transaction/procedure
'#optional close ur connection

oCN.Open sConnectString

meSQL = "CREATE TABLE tbFile1 (FileID1 Long Identity(1,1)"
meSQL = meSQL & ",FIELD1 Text(25)"
meSQL = meSQL & ",FIELD2 Double"
meSQL = meSQL & ",FIELD3 Integer"
meSQL = meSQL & ",FIELD4 Memo"
meSQL = meSQL & ",FIELD5 Date"
meSQL = meSQL & ")"
oCN.Execute meSQL

meSQL = "INSERT INTO tbFile1(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5)"
meSQL = meSQL & "VALUES ("
meSQL = meSQL & "'mae'"
meSQL = meSQL & "," & 1225.01
meSQL = meSQL & "," & 1225
meSQL = meSQL & "," & "'this is a sample of my first lesson creating database and connection'"
meSQL = meSQL & "," & "# 5 - 25 - 2010 #"
meSQL = meSQL & ")"
oCN.Execute meSQL

meSQL = "INSERT INTO tbFile1(FIELD1,FIELD2,FIELD3,FIELD4,FIELD5)"
meSQL = meSQL & "VALUES ("
meSQL = meSQL & "'ann'"
meSQL = meSQL & "," & 925.03
meSQL = meSQL & "," & 925
meSQL = meSQL & "," & "'this is my second lesson populating database database in table'"
meSQL = meSQL & "," & "# 9 - 25 - 2003 #"
meSQL = meSQL & ")"
oCN.Execute meSQL

Set oRS = New ADODB.Recordset
meSQL = "SELECT * FROM tbFile1"

oRS.Open meSQL, oCN, adOpenForwardOnly, adLockReadOnly, -1

If Not oRS.EOF Then
Do Until oRS.EOF
mymsg = "FIELD1=" & Trim(oRS.Fields("FIELD1").Value & "")
mymsg = mymsg & vbCrLf & "FIELD2 =" & Val(Trim(oRS.Fields("FIELD2").Value & ""))
mymsg = mymsg & vbCrLf & "FIELD3 = " & Val(Trim(oRS.Fields("FIELD3").Value & ""))
mymsg = mymsg & vbCrLf & "FIELD4 = " & Trim(oRS.Fields("FIELD4").Value & "")
mymsg = mymsg & vbCrLf & "FIELD5 =" & Val(Trim(oRS.Fields("# FIELD5 #").Value & ""))
'error in highlighted yello for field5 which happen to be for date format. please help
MsgBox mymsg
oRS.MoveNext
Loop
End If
oRS.Close

End Sub

Thank you.

 

[Andrzejek]

On which line of code do you get this error?

Have fun.

---- Andy

 

[Andrzejek]

You got the answer somewhere else, but consider this:

Set oRS = New ADODB.Recordset
meSQL = "SELECT * FROM tbFile1"

With oRS
  .Open meSQL, oCN, adOpenForwardOnly, adLockReadOnly, -1

  If Not .EOF Then
    Do Until .EOF
      mymsg = "FIELD1=" & Trim(!FIELD1.Value & "")
      mymsg = mymsg & vbCrLf & "FIELD2 =" & Val(Trim(!FIELD2.Value & ""))
      mymsg = mymsg & vbCrLf & "FIELD3 = " & Val(Trim(!FIELD3.Value & ""))
      mymsg = mymsg & vbCrLf & "FIELD4 = " & Trim(!FIELD4.Value & "")
      mymsg = mymsg & vbCrLf & "FIELD5 = " & Val(Trim(!FIELD5.Value & ""))
      MsgBox mymsg
     .MoveNext
   Loop
 End If
 .Close
End With

And I would really try to name my fields something better than just Field1, Field2, ... (avoid using spaces - use _ instead, special characters, and reserved words like: Name, Time, Date, etc) I know it is just the beginning, but if you get certain habits now, your life as a programmer later will be much easier.

Have fun.

---- Andy

 

[VB6AccessSQL]

hi Andy, error is INVALID or UNQUALIFIED REFERENCE for !FIELD5
mymsg = mymsg & vbCrLf & "FIELD5 =" & Val(Trim(!FIELD5.Value & ""))

 

[dhookom]

This is probably not a date:
# 9 - 25 - 2003 #
There are 6 spaces too many and the format would be better as yyyy-mm-dd.


Duane
Hook'D on Access
MS Access MVP