Solved

acsess Insert show - from VB

Posted on 2001-07-18
12
190 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:silki
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 3
12 Comments
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6293563
Try

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

Vin.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6293570
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
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6293579
Argh you got there before me...

Vin.
0
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

Author Comment

by:silki
ID: 6293716
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
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6293983
In the Project menu select references.

Move down the list until you find it.

Vin.
0
 

Author Comment

by:silki
ID: 6294062
Ok i now get

run-time erro '3706'

ADO could no t find the specified provider.

on  cnnAccess.Open
0
 

Author Comment

by:silki
ID: 6294063
HHHHHHHHHHHHHHEEEEEEEEEEEEEEEEEEEEEEELLLLLLLLLLLLLLPPPPPPPP
0
 
LVL 4

Expert Comment

by:VincentLawlor
ID: 6294242
You need to download MDAC from

http://www.microsoft.com/data

and install it on your computer.
0
 
LVL 43

Expert Comment

by:TimCottee
ID: 6294253
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
 

Author Comment

by:silki
ID: 6294320
BRILLIAN TimCottee !!

Work fine now

I take it i can now use normal SQl statements like

INSERT
Delete

ETC ....
0
 
LVL 43

Accepted Solution

by:
TimCottee earned 100 total points
ID: 6297310
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
 

Author Comment

by:silki
ID: 6297382
Thanks - A great help !!!
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question