Am I right?

[valchyshen]

I'd like to work with data of a table by VBA code not by MS Access interface. There is another way to do it, than to use this:

Public Sub MySub()
...
DoCmd.Opentable MyTable
...
End Sub

Am I right? Please, advise what code is the best for this?

[chrisjk]

Not quite sure what you mean. VBA is the language of the Access UI. You can access tables via VB only to read/write info.

[valchyshen]

I am developing a db in MS Access 2000. Sometimes I use VBA (by writing modules) to solve certain tasks within my db. For example, I need some info from a table, I think that doing this by writing a simple code is much more easier way than creating a query.

Originally posted by chrisjk
Not quite sure what you mean. VBA is the language of the Access UI. You can access tables via VB only to read/write info.

[Gaffer]

Lets be clear about this. In your first post, it looks like you want to open a table for a user to view and manipulate. if this is the case, then DoCmd.OpenTable is a fairly effective way of doing this in VBA.

In your second post, it seems like you want to only view a certain selection of data. In this case you need a Query.

Is your question "How do I create a query programmatically using VBA?"

[valchyshen]

May be I was wrong in my 1st message, but I my question is "How read data from a table using VBA? I don't want to use DoCmd.Opentable or a Query"

May be I still wrong ... I am sorry, because I want to get quick advise without having a MS Access manual.

Originally posted by Gaffer
Lets be clear about this. In your first post, it looks like you want to open a table for a user to view and manipulate. if this is the case, then DoCmd.OpenTable is a fairly effective way of doing this in VBA.

In your second post, it seems like you want to only view a certain selection of data. In this case you need a Query.

Is your question "How do I create a query programmatically using VBA?"

[Gaffer]

So the final result is going to be a datagrid, in Access, for the user to see data?

[valchyshen]

The final result is to find a record by some criterion, then take the value of a field and compare it with field's value from previous record.

Somebody advised me here to do this by SQL query, but I failed, because an error occured while compiling it.

Somebody gave me this code

Recordset.MoveLast
Recordset.MovePrevious 'next to last
Recordset.MoveNext 'last

Originally posted by Gaffer
So the final result is going to be a datagrid, in Access, for the user to see data?

[Gaffer]

Now we have it! OK, you need a quick tutorial on recordsets. Are you using Access 97?

[valchyshen]

Access 2000

Originally posted by Gaffer
Now we have it! OK, you need a quick tutorial on recordsets. Are you using Access 97?

[Gaffer]

OK, the standard data access tool for 2000 (and everything else for the forseeable future) is ADO.

You need to start a new function, and put this code in:

Dim rec As Recordset
 Set rec = New Recordset
 rec.Source = "Select * From Table1;" 'This is where you put your SQL statement to get the recordset
 rec.ActiveConnection = Currentproject.connection
 rec.Open

This creates a recordset that allows you to step through each line and each field of data. So, if you want to get the value of a field called customer in record one, you use:

rec.MoveFirst
strCust1 = rec("Customer")

and to get line 2:

rec.MoveNext
strCust2 = rec("Customer")

Then you can do your string comaprison.

Also, rec.MovePrevious also exists...

[valchyshen]

Thank you very much for help! I'll try tonight.

Keep in touch,

[Gaffer]

heheh, no, i think YOU'D better keep in touch

[valchyshen]

You are right!

Originally posted by Gaffer
heheh, no, i think YOU'D better keep in touch

[Gaffer]

Originally posted by valchyshen
You are right!

let me know how you get on....

[valchyshen]

Hi!

You know it works very well! This is my code:


Public Sub test()

Dim rec As Recordset
Set rec = New Recordset
rec.Source = "SELECT TOP 2 * FROM pfts_index ORDER BY Date DESC;"
rec.ActiveConnection = CurrentProject.Connection
rec.Open
rec.MoveFirst
CurIndex = rec("Index")
rec.MoveNext
PrvIndex = rec("Index")
DailyIndexChange = ((CurIndex - PrvIndex) / CurIndex) * 100
End Sub

But I'd like to put a date parameter into SQL query, what I have to do, please?

Originally posted by Gaffer

let me know how you get on....

[Gaffer]

looks good. To get a parameter in, first set a variable:

Dim sMyDate as String

Populate the varianle

sMyDate = "21/02/98"

Now, here, I always format to US standards, regardless of the scenario

sMyDate = Format$(sMyDate,"mm/dd/yyyy")

Then the SQL becomes:

rec.Source = "SELECT TOP 2 * FROM pfts_index WHERE Date=#" & sMyDate & "# ORDER BY Date DESC;"

Oh, by the way, I would avoid using Date as a table column name - Date is a reserved word and could be confused with
Date() function. Don't worry too much if you're too far down the road to change it...

[valchyshen]

There is a problem. In the code:

rec.Source = "SELECT top 2 * FROM pfts_index WHERE Date<=# & DateStr & # ORDER BY Date DESC"

the parameter DateStr, which recieved by InputBox function, has following format "21.06.2001". And the code does not work. If I put "21/06/2001" instead of DateStr, it works well. I think the matter is in the date separators ("." and "/"). What I have to do, please?