×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Basic Code... Please Help

Basic Code... Please Help

Basic Code... Please Help

(OP)
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.   

RE: Basic Code... Please Help

   
On which line of code do you get this error?

Have fun.

---- Andy

RE: Basic Code... Please Help

    
You got the answer somewhere else, but consider this:

CODE

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

RE: Basic Code... Please Help

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

RE: Basic Code... Please Help

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close