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.

 

[MajP]

The Access Forums are pretty much viewed by all the same people, so only post once. It gets very confusing if you have a double post. So I would keep this one. In your two other thread say "I have double posted please provide responses in the access modules forum in thread########" This will create a hyperlink to this thread. Then everyone sees everyone elses responses and all answers are in a single area.

 

[MajP]

You cannot just say something like "I want to build an application can someone provide me all the code." Really?
First you need to modularize and encapsulate your code. You will need lots of procedures. Start writing the individual procedures. The event procedures should call these procedures. That way different/multiple object events can call the procedures.

public sub ConnectToDB...

Public sub AddRecord

Public sub DeleteRecord..

Public sub SaveRecord

Public sub RetrieveRecord.

Then start bite this in pieces. Each piece is probably a thread on its own. I could not imagine this being a single thread. The first thing would be to make a connection to the DB. If you cannot do that then maybe that is a thread. Then you need to retrieve data...

 

[MajP]

BTW, those are just examples of individual procedures. I am not saying those are the specific ones, you need to tell us.

 

[JoeAtWork]

Regarding error # 3265, it means that an identifier you have used in a collection doesn't exist. In this case, there is no field called "# FIELD5 #". The names of fields do not get modified with special characters based on the data type. Just use the field name exactly as it is spelled in the database. In this case, I'm pretty sure you just want to call it "FIELD5".

 

[VB6AccessSQL]

MajP thanks! that what exactly i am asking... per command button...

JoeAtWork, thanks also... but #is use for date right? meaning, i will simply code "FIELD5" as is?

tahnks so much...

 

[VB6AccessSQL]

MaJP,

can you please send me the syntax for your following procedures?
public sub ConnectToDB...

Public sub AddRecord

Public sub DeleteRecord..

Public sub SaveRecord

Public sub RetrieveRecord.

thanks in advance...

 

[MajP]

can you please send me the syntax for your following procedures?

Sure. How about I just build a complete application and deliver it to your customer? I will just rap it in a bow and say it is from you, and give them a bill to mail to you.

Sorry for the sarcasm, but that is not how this site works or the real world. Read the rules of the site. If you ask another question like that expect to be black balled from Tek Tips.

Spend some time. Show some effort, and post a specific question about a specific problem. Do not ask for a hand-out.