silki
asked on
acsess Insert show - from VB
Hello i have got a simple form with one text box on it
and a acess 2000 db with one table called main with
id and name as fields
i wont to be able to hard code the name in the code but let the number for the id table come from the form ??
i also would like a serach so number = 1 show name ??
SILKI
and a acess 2000 db with one table called main with
id and name as fields
i wont to be able to hard code the name in the code but let the number for the id table come from the form ??
i also would like a serach so number = 1 show name ??
SILKI
Add a reference to Microsoft Activex Data Object 2.x Library to your project. At least version 2.1 or higher is required for access 2000 databases:
Here is a very simple example which opens the connection on form load and closes it with the form. When you click the command button, the contents of text1 are used in the where clause of the select statement to retrieve the corresponding record and the value of the name field is put into text2. This can be expanded on at will but should give you an idea of how to proceed.
Private cnnAccess As ADODB.Connection
Private Sub Form_Load()
Set cnnAccess = New ADODB.Connection
cnnAccess.ConnectionString = "Provider=Jet.OLEDB.4.0;Da ta Source=c:\MyFolder\MyMDB.m db"
cnnAccess.Open
End Sub
Private Sub Form_Unload()
cnnAccess.Close
Set cnnAccess = Nothing
End Sub
Private Sub Command1_Click()
Dim rstMain As ADODB.Recordset
Set rstMain = New ADODB.Recordset
rstMain.Open "Select Name From Main Where ID = " & CStr(Text1.Text),cnnAccess ,adOpenSta tic,adLock ReadOnly
Text2.Text = rstMain.Fields("Name").Val ue
rstMain.Close
Set rstMain = Nothing
End Sub
Here is a very simple example which opens the connection on form load and closes it with the form. When you click the command button, the contents of text1 are used in the where clause of the select statement to retrieve the corresponding record and the value of the name field is put into text2. This can be expanded on at will but should give you an idea of how to proceed.
Private cnnAccess As ADODB.Connection
Private Sub Form_Load()
Set cnnAccess = New ADODB.Connection
cnnAccess.ConnectionString
cnnAccess.Open
End Sub
Private Sub Form_Unload()
cnnAccess.Close
Set cnnAccess = Nothing
End Sub
Private Sub Command1_Click()
Dim rstMain As ADODB.Recordset
Set rstMain = New ADODB.Recordset
rstMain.Open "Select Name From Main Where ID = " & CStr(Text1.Text),cnnAccess
Text2.Text = rstMain.Fields("Name").Val
rstMain.Close
Set rstMain = Nothing
End Sub
Argh you got there before me...
Vin.
Vin.
ASKER
How do i do this ???
"Add a reference to Microsoft Activex Data Object 2.x Library to your project. At least version 2.1 or
higher is required for access 2000 databases:"
"Add a reference to Microsoft Activex Data Object 2.x Library to your project. At least version 2.1 or
higher is required for access 2000 databases:"
In the Project menu select references.
Move down the list until you find it.
Vin.
Move down the list until you find it.
Vin.
ASKER
Ok i now get
run-time erro '3706'
ADO could no t find the specified provider.
on cnnAccess.Open
run-time erro '3706'
ADO could no t find the specified provider.
on cnnAccess.Open
ASKER
HHHHHHHHHHHHHHEEEEEEEEEEEE EEEEEEEEEE ELLLLLLLLL LLLLLPPPPP PPP
I missed a bit from the connection string, use this as your connection string, but substitute your database and path instead of nwind.mdb
cnnAccess.ConnectionString = "Provider=Microsoft.Jet.OL EDB.4.0;Da ta Source=D:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;Pers ist Security Info=False"
cnnAccess.ConnectionString
ASKER
BRILLIAN TimCottee !!
Work fine now
I take it i can now use normal SQl statements like
INSERT
Delete
ETC ....
Work fine now
I take it i can now use normal SQl statements like
INSERT
Delete
ETC ....
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 - A great help !!!
SELECT Name FROM Main WHERE id = Val(Text1.Text)
Vin.