Question about Microsoft Office Standard for PC

1 Answer

Ms Access - OpenRecordset question

Hi, I made the following recordset: Set rst = db.OpenRecordset(str_Query, dbOpenDynaset) This recordset will get me 30 fields. I want to retrieve them and put the result in an array. For example: Dim Col_Title(1 To 30) As String Col_Title(1) = rst![F1] Col_Title(2) = rst![F2] ........................ Col_Title(30) = rst![F30] Now, my qustion is, instead of writing 30 lines to retrive the 30 values from the recordset, how can I write a FOR loop and get the values in least lines of code. For example: For iCount = 1 To 30 iCount = 1 Col_Title(1) = rst![F + iCount] Please suggest me how to code: Col_Title(1) = rst![F + iCount]

Posted by on

Ad

1 Answer

  • Level 3:

    An expert who has achieved level 3 by getting 1000 points

    All-Star:

    An expert that got 10 achievements.

    MVP:

    An expert that got 5 achievements.

    Vice President:

    An expert whose answer got voted for 100 times.

  • Master
  • 883 Answers

I think you just need to change it to read: Col_Title(iCount) = rst![F + iCount] from the original Col_Title(1) = rst![F + iCount]

Posted on Sep 20, 2007

  • Marty marty Sep 27, 2007

    any progress on this solution so far?

  • Marty marty Sep 27, 2007

    any progress on this solution so far?

×

Ad

1 Suggested Answer

6ya6ya
  • 2 Answers

SOURCE: I have freestanding Series 8 dishwasher. Lately during the filling cycle water hammer is occurring. How can this be resolved

Hi,
A 6ya expert can help you resolve that issue over the phone in a minute or two.
Best thing about this new service is that you are never placed on hold and get to talk to real repairmen in the US.
The service is completely free and covers almost anything you can think of (from cars to computers, handyman, and even drones).
click here to download the app (for users in the US for now) and get all the help you need.
Good luck!

Posted on Jan 02, 2017

Ad

Add Your Answer

Uploading: 0%

my-video-file.mp4

Complete. Click "Add" to insert your video. Add

×

Loading...
Loading...

Related Questions:

1 Answer

How to connect MS access in the visual basic?


This following code to connect vb 6 with access
place this code in module :
vb Syntax
  1. Global Conn As ADODB.Connection
  2. Global rs As ADODB.Recordset
  3. Sub Access_Connector()
  4. Set Conn = New ADODB.Connection
  5. Conn.Provider = "microsoft.jet.oledb.4.0"
  6. Conn.CursorLocation = adUseClient
  7. Conn.Open App.Path & "\SIS.mdb"
  8. End Sub
Below link might help you... http://www.functionx.com/vbaccess/Lesson01.htm

Please rate or vote me , if you like solution..

Thanks
sandeep

Mar 15, 2011 | Computers & Internet

1 Answer

How can i connect a microsoft acces from a file in my visual basic to make it both work?


Public db As Database Public rst As Recordset
Set db = OpenDatabase(App.Path & "\product.mdb")

Oct 08, 2010 | Microsoft Visual Basic Enterprise Edition...

1 Answer

Saving the info that i entered in my visual basic in microsoft access.. how to code the save???


check this out
Rst.Open "users", strConnect, , , adCmdTable With Rst .AddNew .Fields("username") = txtname.Text .Fields("User_Password") = txtpws.Text .Fields("Access_Type") = cmbUserTypes.Text .Fields("Date_Created") = Now .Update End With Rst.Close

'#### connection to databas
Public connConnection As New ADODB.Connection Public rsRecordSet As New ADODB.Recordset Public rsRecordSet1 As New ADODB.Recordset Public Rst As New ADODB.Recordset Public Rst1 As New ADODB.Recordset Public Rst2 As New ADODB.Recordset Public Rst3 As New ADODB.Recordset Public strConnect As String Public ConnectionOk As Boolean


Public Sub ConnectionMOd() Dim strProvider As String Dim strDataSource As String Dim strDataBaseName As String Dim i As Integer On Error GoTo ErrorTimer strProvider = "provider=Microsoft.Jet.OLEDB.4.0;" strDataSource = App.path ' strDataBaseName = "Z:\database.mdb" ' strDataSource = App.Path strDataBaseName = "\database.mdb" strDataSource = "Data Source= " & strDataSource & strDataBaseName strConnect = strProvider & strDataSource
Set connConnection = New ADODB.Connection connConnection.CursorLocation = adUseClient connConnection.Open strConnect
Set Rst = New ADODB.Recordset Rst.CursorType = adOpenStatic Rst.CursorLocation = adUseClient Rst.LockType = adLockOptimistic ' adLockOptimistic
Set Rst1 = New ADODB.Recordset Rst1.CursorType = adOpenStatic Rst1.CursorLocation = adUseClient Rst1.LockType = adLockOptimistic
Set Rst2 = New ADODB.Recordset Rst2.CursorType = adOpenStatic Rst2.CursorLocation = adUseClient Rst2.LockType = adLockOptimistic Set Rst3 = New ADODB.Recordset Rst3.CursorType = adOpenStatic Rst3.CursorLocation = adUseClient Rst3.LockType = adLockOptimistic ConnectionOk = True Exit Sub ErrorTimer: MsgBox "Error Connecting To DataBase Please Check Network path", vbCritical, "Connection Error" End End Sub

Jan 08, 2010 | Microsoft Visual Basic Enterprise Edition...

2 Answers

Printing syntax


'declare the variables
Dim Connection
Dim ConnString
Dim Recordset
Dim SQL

'define the connection string, specify database driver
ConnString="DRIVER={SQL Server};SERVER=yourServername;UID=yourUsername;" & _
"PWD=yourPassword;DATABASE=yourDatabasename"

'declare the SQL statement that will query the database
SQL = "SELECT * FROM TABLE_NAME"

'create an instance of the ADO connection and recordset objects
Set Connection = Server.CreateObject("ADODB.Connection")
Set Recordset = Server.CreateObject("ADODB.Recordset")

'Open the connection to the database

Connection.Open ConnString

'Open the recordset object executing the SQL statement and return records
Recordset.Open SQL,Connection

'first of all determine whether there are any records
If Recordset.EOF Then
Response.Write("No records returned.")
Else
'if there are records then loop through the fields
Do While NOT Recordset.Eof
Response.write Recordset("FIRST_FIELD_NAME")
Response.write Recordset("SECOND_FIELD_NAME")
Response.write Recordset("THIRD_FIELD_NAME")
Response.write "<br>"
Recordset.MoveNext
Loop
End If


'close the connection and recordset objects to free up resources
Recordset.Close
Set Recordset=nothing
Connection.Close
Set Connection=nothing

May 16, 2009 | Microsoft Windows Vista Ultimate Edition

1 Answer

How to add items in combobox using database in vb6.0


There are 2 ways:-

you can set the recordsource in the properties for the comboBox to a table or create a recordset and loop through it adding one item at a time, see example below:-


Dim dept As String
Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim qd As DAO.QueryDef
Dim varResults
Dim oMessage
Dim sQueryName As String
Set db = CurrentDb
mdbname.[Form_Name].Department.SetFocus
dept = formname[Form_Name].Department.Text
sQueryName = "Query_Name"
Set qd = db.QueryDefs(sQueryName)
'
' set up input field for query
'
qd.Parameters("field_from_query") = dept
Set rst = qd.OpenRecordset()
Dim recCount As Integer
recCount = rst.RecordCount
If recCount > 0 Then
With rst
.MoveLast
.MoveFirst
Do While Not .EOF
listBoxproducts.AddItem .Fields("pname")
listboxCode.AddItem .Fields("pcode")
listboxPrice.AddItem .Fields("pprice")
.MoveNext
Loop
End With
rst.Close
Set rst = Nothing
Set qd = Nothing
Set db = Nothing

Jan 13, 2009 | Computers & Internet

2 Answers

Vb variable error


Change the code:

strdbname = "d:\vivek\company.mdb"
Set DB = DBEngine.OpenDatabase("D:\VIVEK\COMPANY.MDB")
strrsname = "company"
Set RS = DB.OpenRecordset("COMPANY")
comp.Text = RS!COM_CODE
compnm.Text = RS!COMPANY
isinno.Text = RS!ISIN
AD1.Text = RS!ADD1
'AD2.Text = RS!ADD2
'AD3.Text = RS!ADD3
AD4.Text = RS!ADD4
BK.Text = RS!BOOK
'PG.Text = RS!Page
to become:

strdbname = "d:\vivek\company.mdb"
Set DB = DBEngine.OpenDatabase(strdbname )
strrsname = "company"
Set RS = DB.OpenRecordset(strrsname )
comp.Text = RS!COM_CODE
compnm.Text = RS!COMPANY
isinno.Text = RS!ISIN
AD1.Text = RS!ADD1
AD2.Text = RS!ADD2
AD3.Text = RS!ADD3
AD4.Text = RS!ADD4
BK.Text = RS!BOOK
PG.Text = RS!Page
mostly to eliminate the extra "quote" (') characters,
and to use the values you set.


Dec 10, 2008 | Computers & Internet

1 Answer

Am not able to add/ retrive data


Proper syntax is:
With rstTemp
.AddNew
!FirstName = strFirst
!LastName = strLast
.Update
End With

Do you have the ADODB library loaded?

While in "Code" view...menu/tools/references ... look for a check by "Microsoft ActiveX Data Objects x.xx..."

If you don't have ADO reference library then setting ADODB recordset won't work no matter what syntax you use.

Hint: try using Ctrl+J while you code. The library will show you available syntax as you type.

Jul 20, 2008 | Microsoft Windows XP Professional

1 Answer

Conversion of nemeric value


If you just want to change the format to text in a query, you can use the Str function in your query. In MS Access, in the Field area of your query in Design View, you can type the following:
YourDesiredCharFieldName: Str([YourNumericFieldName]).

On the other hand, if you prefer to convert the data once and for all in your table, you can go to the Table Design View for that Table, change the Data Type of your numeric field to "Text", and close that window to save the changes. This will convert all of your data in that field from numeric to text permanently.

Jul 01, 2008 | Microsoft Office Access 2003 (077-02871)...

1 Answer

Export data in excel shld yoeet through VB


When i first figured out how to pull data from SQL and put the results in an excel file i referenced these two articles....
Reading and writing excel file using VB.NET (http://www.codeproject.com/KB/vb/Work_with_Excel__VBNET_.aspx)
Get the Values From DataBase and Stored into excell Sheet (http://www.codeproject.com/KB/vb/Getvaluesfromdatabase.aspx)

This is the code i ended up using.... (check out those links to see how you need to import the ms office excel reference file with visual basic)

Const stcon As String = "Provider=SQLNCLI;server=xxxxx;database=xxxxx;uid=xxxxx;pwd=xxxxx;DataTypeCompatibility=80"
Dim stSQL As String = "select * from scs_rate_class_money where irate_book = 124 and snew_used = 'U' and sclass = '2' and splan = 'T4' and sopt_code = 'F1'"
Dim cnt As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
'Open the connection.
cnt.Open(stcon)

'Open the recordset.
With rst
.CursorLocation = ADODB.CursorLocationEnum.adUseClient
.Open(stSQL, cnt, ADODB.CursorTypeEnum.adOpenForwardOnly, _
ADODB.LockTypeEnum.adLockReadOnly , _
ADODB.CommandTypeEnum.adCmdText)
.ActiveConnection = Nothing 'Disconnect the Recordset.
End With
'Close the connection
cnt.Close ()
Dim exp As Export = New Export()
Dim xlApp As New Microsoft.Office.Interop.Excel.Application
Dim xlWBook As Microsoft.Office.Interop.Excel.Workbook = xlApp.Workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet )
Dim xlWSheet As Microsoft.Office.Interop.Excel.Worksheet = CType(xlWBook.Worksheets(1), Microsoft.Office.Interop.Excel.Worksheet)
Dim xlRange As Microsoft.Office.Interop.Excel.Range = CType(xlWSheet, Microsoft.Office.Interop.Excel.Worksheet).Range("A2")
Dim xlCalc As Microsoft.Office.Interop.Excel.XlCalculation
Dim i As Short

'Turn off Excel's calculation.
With xlApp
xlCalc = .Calculation
.Calculation = Microsoft.Office.Interop.Excel.XlCalculation.xlCalculationManual
End With
'Write the fieldnames.
For Each fld In rst.Fields
xlRange.Offset(0, i).Value = fld.Name
i = i + 1
Next
'Populate the range.
xlRange.Offset(1, 0).CopyFromRecordset(rst)
'Close the recordset.
rst.Close()
'Make Excel available to the user.
With xlApp
.Visible = True
.UserControl = True
'Restore the calculation mode.
.Calculation = xlCalc
End With
'Release variables from memory.
fld = Nothing
rst = Nothing
cnt = Nothing
xlRange = Nothing
xlWSheet = Nothing
xlWBook = Nothing
xlApp = Nothing

Jan 03, 2008 | Computers & Internet

1 Answer

Ms access


Stored Procedures don't exist in MS Access. You do have the following options though. Queries, which are just select or action queries. VB Code, which you can write using Query Objects as if you were writing a Visual Basic application. These can be Functions or Subroutines. In these, you would use the standard programming techniques with while and for loops, etc.

You can create complex situations combining the two of these. A function in the VB code area can accept through parameters, the single values (line by line, record by record) in a query and act on them and manipulate the values.

For example: create a query that does a select phone from address. Create a function in vb called public function StripDashesInPhone(Phone as string) which then uses VB coding to strip dashes from each phone value passed in. To make it all work, in the query on one of the field columns put "NewPhone: StripDashesInPhone([Phone])" and for every record processed in the query, the function is called with the [Phone] field value passed in to the function and the action is processed and returned.

Other than writing a function that is activated by a form button click, which opens the current db and opens a table and process it, just like in VB, this is about as close to cursors and oracle procedures as you get.

I wish it was more, but ...

Oct 09, 2007 | Microsoft Office Standard for PC

Not finding what you are looking for?
Microsoft Office Standard for PC Logo

Related Topics:

194 people viewed this question

Ask a Question

Usually answered in minutes!

Top Microsoft Computers & Internet Experts

micky dee

Level 3 Expert

2983 Answers

Piyal Perera
Piyal Perera

Level 3 Expert

528 Answers

Les Dickinson
Les Dickinson

Level 3 Expert

18424 Answers

Are you a Microsoft Computer and Internet Expert? Answer questions, earn points and help others

Answer questions

Manuals & User Guides

Loading...