Access the database using VBA code

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.

re: Access the database using VBA code

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" <chunchum2004@yahoo.com> wrote in message
news:cd5c6e10.0411010940.4de455cb@posting.google.c om...[color=blue]
> 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.[/color]

re: Access the database using VBA code

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" <chunchum2004@yahoo.com> wrote in message
news:cd5c6e10.0411010940.4de455cb@posting.google.c om...[color=blue]
> 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.[/color]

re: Access the database using VBA code

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" <bouncer@localhost.not> wrote in message news:<fGDhd.9047$vB.942@trnddc03>...[color=blue]
> 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" <chunchum2004@yahoo.com> wrote in message
> news:cd5c6e10.0411010940.4de455cb@posting.google.c om...[color=green]
> > 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.[/color][/color]

re: Access the database using VBA code

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\)" <classicnickNOSPAMAT@MASPON.yahooDOTcom> wrote in message news:<41867cce$0$43613$ed2e19e4@ptn-nntp-reader04.plus.net>...[color=blue]
> 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" <chunchum2004@yahoo.com> wrote in message
> news:cd5c6e10.0411010940.4de455cb@posting.google.c om...[color=green]
> > 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.[/color][/color]

re: Access the database using VBA code

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



chunchum2004@yahoo.com (Chand) wrote in message news:<cd5c6e10.0411010940.4de455cb@posting.google. com>...[color=blue]
> 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.[/color]