Read Access Database in VB 3

[browneye]

Hi,
In my access database I am stroing some info in caption. You can see that information if you
open access database
click on design view
and see property caption of that field.
I have two questions
1>How can I read this value using ADO ?
2>Is there way to store information like that in SQL Server 7.0 and Oracle 8 ?

thank you in advance

 

[MichaelRed]

Basically, I don't believe you can. You can, of course, "run" Ms. Access from VB. I would suggest that you create a table in the Ms. Access app to store the values you (may) want to retrieve from VB. Then, just treat the whole process as another recordset.

I have occassionally set up a table in the form of:

VarName[tab][tab]VarValue

for similar purposes. In VB (or any database/table aware) program, Just search for VarName in hte recordset, retrieve the VarValue of the record. For instance you might (in your example) have MyFormCaption as the VarName and "This is the Main Form" in the VarValue.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[KirkJewell]

To connect to an Access Database using ADO you have to do one of the following:-

((Firstly get a book on ADO2.5 (and makes sure you have that version at least).))

Either connect manualy (vbcode). I haven't got the time and energy to go into detail. I would get a book 'SAMS Teach yourself ADO2.5 in 21 Days is very good.

(You need to create a ADODB.connection to your database and then you can get to the properties of tables, fields etc.

Or Use the ADO Data Contol. (You may need to add it from you components list (right click on toolbox).
Goto the properties of the ADO control and there is a mini wizard to connect it to your database. It creates the correct connection string for you. Also don't forget to assign it a table or query.

Once you have the ADO Data Control working (you can test it by putting a txtField on a form and bind it to one of the fields using the ADO Data Control. Clicking the arrows on the control will step you through the recordset.

You can obtain a clone of the recordset and its properties by assign properties of the ADO Data Control to variables, e.g dim rs as recordset

set rs = adodc1.recordset.clone

.................
I can't quite work out why you would want to reference the captions of fields in a database (other than as part of normal recordset work). So I'm assuming you are storing data in the table as well. Once you have a table of data you wouldn't normally change captions programmatically.

The only reason I can think of is to batch change captions during a patch/code update.

If you don't want to change the field captions but just read them you can do this with the properties of an ADO recordset.

Regards
Kirk.

 

[MichaelRed]

OOps. Sorry. Kirk is right. I misread your question and thought you were attempting to refer to a caption of a Control on a form/report.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[browneye]

Kirk,
thank you for responding. What I was trying to do is,
In my app when user goes to data entry field , I display short message about it in status bar.
I was thinking to store message to caption property of that field. Same way I was thinking storing error message in validation text.
the point is how can I retrive text using VB and ADO. I understand , Im not sure though you can do this in DAO.

thank you, appriciated.

 

[JWhyte]

The following code will display all the 'Descriptions' for fields in a particular Access table.

Dim adoConn as ADODB.Connection
Dim adoRST as ADODB.Recordset

set adoConn = New ADODB.Connection
adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentDB.Name & ";"

' Substitute [b]<Table Name>[/b] for the name of the access table
' that you wish to display the Description/Caption for.

set adoRst = adoConn.OpenSchema(adoSchemaColumns,Array(Empty,Empty,&quot;[b]<Table Name>[/b]&quot;,Empty)

While not adoRst.EOF
  Debug.Print adoRst!TABLE_NAME & space(1) & adoRst!COLUMN_NAME & Space(1) & adoRst!DESCRIPTION
  adoRst.MoveNext
wend

adoRst.Close
adoConn.Close

This code will display the Table Name, Column Name and Description the table you specify.

I hope this provides you with a good starting point.
John Whyte
jwhyte@skipton.co.uk

 

[browneye]

John,
sounds great. I will try this and let you know. One more question though. How I can store these values in SQL Sever 7.0?
thank you. appriciated.

 

[edderic]

Wtth DTS wizzard in SQLServer7.0 and then Import Table From Access Eric De Decker
vbg.be@vbgroup.nl

Licence And Copy Protection AxtiveX.

Download Demo version on my Site:
Promotions before 02/28/2001 (free source codebook),visite my site

http://www.createsoftware2000.com

 

[browneye]

Kool. Thank you Eric. Appriciated

 

[bhai1]

hi,
In ADO programming ,i have a StoredProcedure in SQL Server with One Input Parameter. how i should execute the StoredProcedure with ADO Command Object?

 

[browneye]

Here is Example of Stored Procedure and ADO Command Object.


Dim cnn1 As ADODB.Connection
Dim cmdByRoyalty As ADODB.Command
Dim prmByRoyalty As ADODB.Parameter
Dim rstByRoyalty As ADODB.Recordset
Dim rstAuthors As ADODB.Recordset
Dim intRoyalty As Integer
Dim strAuthorID As String
Dim strCnn As String

' Define a command object for a stored procedure.
Set cnn1 = New ADODB.Connection
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=pubs;User Id=sa;Password=; "
cnn1.Open strCnn
Set cmdByRoyalty = New ADODB.Command
Set cmdByRoyalty.ActiveConnection = cnn1
cmdByRoyalty.CommandText = "byroyalty"
cmdByRoyalty.CommandType = adCmdStoredProc
cmdByRoyalty.CommandTimeout = 15

' Define the stored procedure's input parameter.
intRoyalty = Trim(InputBox( _
"Enter royalty:&quot)
Set prmByRoyalty = New ADODB.Parameter
prmByRoyalty.Type = adInteger
prmByRoyalty.Size = 3
prmByRoyalty.Direction = adParamInput
prmByRoyalty.Value = intRoyalty
cmdByRoyalty.Parameters.Append prmByRoyalty

' Create a recordset by executing the command.
Set rstByRoyalty = cmdByRoyalty.Execute()

' Open the Authors table to get author names for display.
Set rstAuthors = New ADODB.Recordset
rstAuthors.Open "authors", strCnn, , , adCmdTable

' Print current data in the recordset, adding
' author names from Authors table.
Debug.Print "Authors with " & intRoyalty & _
" percent royalty"
Do While Not rstByRoyalty.EOF
strAuthorID = rstByRoyalty!au_id
Debug.Print , rstByRoyalty!au_id & ", ";
rstAuthors.Filter = "au_id = '" & strAuthorID & "'"
Debug.Print rstAuthors!au_fname & " " & _
rstAuthors!au_lname
rstByRoyalty.MoveNext
Loop

rstByRoyalty.Close
rstAuthors.Close
cnn1.Close