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
silkiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

VincentLawlorCommented:
Try

SELECT Name FROM Main WHERE id = Val(Text1.Text)

Vin.
0
TimCotteeHead of Software ServicesCommented:
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;Data Source=c:\MyFolder\MyMDB.mdb"
  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,adOpenStatic,adLockReadOnly
  Text2.Text = rstMain.Fields("Name").Value
  rstMain.Close
  Set rstMain = Nothing
End Sub
0
VincentLawlorCommented:
Argh you got there before me...

Vin.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

silkiAuthor Commented:
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:"
0
VincentLawlorCommented:
In the Project menu select references.

Move down the list until you find it.

Vin.
0
silkiAuthor Commented:
Ok i now get

run-time erro '3706'

ADO could no t find the specified provider.

on  cnnAccess.Open
0
silkiAuthor Commented:
HHHHHHHHHHHHHHEEEEEEEEEEEEEEEEEEEEEEELLLLLLLLLLLLLLPPPPPPPP
0
VincentLawlorCommented:
You need to download MDAC from

http://www.microsoft.com/data

and install it on your computer.
0
TimCotteeHead of Software ServicesCommented:
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.OLEDB.4.0;Data Source=D:\Program Files\Microsoft Visual Studio\VB98\Nwind.mdb;Persist Security Info=False"
0
silkiAuthor Commented:
BRILLIAN TimCottee !!

Work fine now

I take it i can now use normal SQl statements like

INSERT
Delete

ETC ....
0
TimCotteeHead of Software ServicesCommented:
You can use any sql statements you like, there are a number of different ways of using them though, some are better than others for different things:

rstMain.Open "Select * From Main",cnnAccess,adOpenStatic,adLockOptimistic

For example returns a recordset, you can choose whether it is a keyset/dynamic/static recordset and also the locking type.

cnnAccess.Execute "INSERT INTO Main (ID,Name) Values(400,'TimCottee')"

Executes a sql statement on the connection but doesn't return a recordset, you can use the execute method to return a recordset as well but it is forward-only/read-only and may not be much use to you.

Anyway this should be enough to get you started, you should probably close this question now, and open new ones for any subsequent specific problems that you have.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
silkiAuthor Commented:
Thanks - A great help !!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.