By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,874 Members | 1,058 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,874 IT Pros & Developers. It's quick & easy.

Access the database using VBA code

P: n/a
Private Sub tbDate_AfterUpdate()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
rst.CursorLocation = adUseClient

Set cnn = CurrentProject.Connection
rst.Open "SELECT * FROM Table1 ORDER BY StudentID ASC", cnn,
adOpenStatic, adLockReadOnly, adCmdText
rst.Close
cnn.Close

End Sub
I have been trying to sort the database through the VBA code. The
above SQL statement works very well when I do it in the SQL view or
Design view of Query. Somehow I am not able to access the database
through VBA code; I don't understand where I am going wrong. Can
anybody please help me? I am using Access 2003 with OS as Win 2003
professional. Do I need to create some kind of User DSN or File DSN,
if so can you please give me the procedure or any website where I can
learn to do it.
Any help in this regard will be highly appreciated
Thank you so much.
Nov 13 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
This may be me misunderstanding or a syntactical error
but...

The database itself doesn't get sorted or re-arranged.

When you run an ordered query or view rows on a form you are
looking through a window at a virtual view of the records
(rows) not a re-ordering of the records themselves. The
apparently re-ordered rows from a table are an abstraction.

The code you included opens an ADO recordset then closes it.
It doesn't actually do anything with it.

Could you refine your question?

--
Nick Coe (UK)
AccHelp v1.01 Application Help File Builder
http://www.alphacos.co.uk/
Download Free Demo Copy
----
"Chand" <ch**********@yahoo.com> wrote in message
news:cd**************************@posting.google.c om...
Private Sub tbDate_AfterUpdate()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
rst.CursorLocation = adUseClient

Set cnn = CurrentProject.Connection
rst.Open "SELECT * FROM Table1 ORDER BY StudentID ASC",
cnn,
adOpenStatic, adLockReadOnly, adCmdText
rst.Close
cnn.Close

End Sub
I have been trying to sort the database through the VBA
code. The
above SQL statement works very well when I do it in the
SQL view or
Design view of Query. Somehow I am not able to access the
database
through VBA code; I don't understand where I am going
wrong. Can
anybody please help me? I am using Access 2003 with OS as
Win 2003
professional. Do I need to create some kind of User DSN
or File DSN,
if so can you please give me the procedure or any website
where I can
learn to do it.
Any help in this regard will be highly appreciated
Thank you so much.

Nov 13 '05 #2

P: n/a
Just for the record, there is no advantage of using ADO code rather than DAO
code to work with Jet (the default database engine used with Access). Quite
the contrary, DAO is the native language of Jet, is faster than ADO for Jet,
and is more complete than ADO for Jet.

And, using it won't give you a "headstart on the future" because its
successor, ADO.NET, is already available (in the .NET framework), is not
compatible, and has a different object model.

Larry Linson
Microsoft Access MVP

"Chand" <ch**********@yahoo.com> wrote in message
news:cd**************************@posting.google.c om...
Private Sub tbDate_AfterUpdate()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
rst.CursorLocation = adUseClient

Set cnn = CurrentProject.Connection
rst.Open "SELECT * FROM Table1 ORDER BY StudentID ASC", cnn,
adOpenStatic, adLockReadOnly, adCmdText
rst.Close
cnn.Close

End Sub
I have been trying to sort the database through the VBA code. The
above SQL statement works very well when I do it in the SQL view or
Design view of Query. Somehow I am not able to access the database
through VBA code; I don't understand where I am going wrong. Can
anybody please help me? I am using Access 2003 with OS as Win 2003
professional. Do I need to create some kind of User DSN or File DSN,
if so can you please give me the procedure or any website where I can
learn to do it.
Any help in this regard will be highly appreciated
Thank you so much.

Nov 13 '05 #3

P: n/a
Thanks Larry for your help.
Unfortunately I am not able to access or modify my database through
VBA code using DAO.
Can you please give me a sample VBA code to access/modify database for
Access 2003.
Appreciate your tips.

"Larry Linson" <bo*****@localhost.not> wrote in message news:<fGDhd.9047$vB.942@trnddc03>...
Just for the record, there is no advantage of using ADO code rather than DAO
code to work with Jet (the default database engine used with Access). Quite
the contrary, DAO is the native language of Jet, is faster than ADO for Jet,
and is more complete than ADO for Jet.

And, using it won't give you a "headstart on the future" because its
successor, ADO.NET, is already available (in the .NET framework), is not
compatible, and has a different object model.

Larry Linson
Microsoft Access MVP

"Chand" <ch**********@yahoo.com> wrote in message
news:cd**************************@posting.google.c om...
Private Sub tbDate_AfterUpdate()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
rst.CursorLocation = adUseClient

Set cnn = CurrentProject.Connection
rst.Open "SELECT * FROM Table1 ORDER BY StudentID ASC", cnn,
adOpenStatic, adLockReadOnly, adCmdText
rst.Close
cnn.Close

End Sub
I have been trying to sort the database through the VBA code. The
above SQL statement works very well when I do it in the SQL view or
Design view of Query. Somehow I am not able to access the database
through VBA code; I don't understand where I am going wrong. Can
anybody please help me? I am using Access 2003 with OS as Win 2003
professional. Do I need to create some kind of User DSN or File DSN,
if so can you please give me the procedure or any website where I can
learn to do it.
Any help in this regard will be highly appreciated
Thank you so much.

Nov 13 '05 #4

P: n/a
Thanks Nick for your tips. I was not aware that the database could not
be sorted.
Also to refine my question, all I want to do is to access/modify my
database using VBA code. Like can i insert a record into the database
using VBA code, if so, how can I do that ? Can you or any
knowledgeable person than me could help me out with this problem, I
would really appreciate it.
Thanks again Nick.
"Nick Coe \(UK\)" <cl*****************@MASPON.yahooDOTcom> wrote in message news:<41***********************@ptn-nntp-reader04.plus.net>...
This may be me misunderstanding or a syntactical error
but...

The database itself doesn't get sorted or re-arranged.

When you run an ordered query or view rows on a form you are
looking through a window at a virtual view of the records
(rows) not a re-ordering of the records themselves. The
apparently re-ordered rows from a table are an abstraction.

The code you included opens an ADO recordset then closes it.
It doesn't actually do anything with it.

Could you refine your question?

--
Nick Coe (UK)
AccHelp v1.01 Application Help File Builder
http://www.alphacos.co.uk/
Download Free Demo Copy
----
"Chand" <ch**********@yahoo.com> wrote in message
news:cd**************************@posting.google.c om...
Private Sub tbDate_AfterUpdate()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
rst.CursorLocation = adUseClient

Set cnn = CurrentProject.Connection
rst.Open "SELECT * FROM Table1 ORDER BY StudentID ASC",
cnn,
adOpenStatic, adLockReadOnly, adCmdText
rst.Close
cnn.Close

End Sub
I have been trying to sort the database through the VBA
code. The
above SQL statement works very well when I do it in the
SQL view or
Design view of Query. Somehow I am not able to access the
database
through VBA code; I don't understand where I am going
wrong. Can
anybody please help me? I am using Access 2003 with OS as
Win 2003
professional. Do I need to create some kind of User DSN
or File DSN,
if so can you please give me the procedure or any website
where I can
learn to do it.
Any help in this regard will be highly appreciated
Thank you so much.

Nov 13 '05 #5

P: n/a
If you are looking for a decent reference guide to DAO as mentioned by
Larry, you may be interested in this book. It's really dirt cheap in
$$ and is much the same in 's (if you are paying in Sterling).

http://www.amazon.com/exec/obidos/tg...books&n=507846

I think it's the best 4 + p & p I have spent in ages.

;-)

D

ch**********@yahoo.com (Chand) wrote in message news:<cd**************************@posting.google. com>...
Private Sub tbDate_AfterUpdate()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
rst.CursorLocation = adUseClient

Set cnn = CurrentProject.Connection
rst.Open "SELECT * FROM Table1 ORDER BY StudentID ASC", cnn,
adOpenStatic, adLockReadOnly, adCmdText
rst.Close
cnn.Close

End Sub
I have been trying to sort the database through the VBA code. The
above SQL statement works very well when I do it in the SQL view or
Design view of Query. Somehow I am not able to access the database
through VBA code; I don't understand where I am going wrong. Can
anybody please help me? I am using Access 2003 with OS as Win 2003
professional. Do I need to create some kind of User DSN or File DSN,
if so can you please give me the procedure or any website where I can
learn to do it.
Any help in this regard will be highly appreciated
Thank you so much.

Nov 13 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.