 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
VB Forum / General 2 / October 2003Beginner SQL question
Psychological block! I just need a push to get started Suppose I want to put an original record table in alphabetical order, how do I do it with a vb subroutine? I have a file phonebook.mdb in it there is a table phonebook and one of the columns is called Surname Something like: sub ABC() MySource="c:\myproject\phonebook.mdb" Data1.RecordSource="MySource Order By Surname" End Sub - I know that isn't right. I don't necessarily want to attatch it to a data control ................ Is there a website that'll tell me the very beginnings; not so much the detail of sql; more how to slot it in to vb? Thanks Tony > Psychological block! I just need a push to get started > Suppose I want to put an original record table in alphabetical order, [quoted text clipped - 19 lines] > > Tony Using DAO or ADO ? > > Psychological block! I just need a push to get started > > Suppose I want to put an original record table in alphabetical order, [quoted text clipped - 21 lines] > > Using DAO or ADO ? Doesn't really matter if he want to do it with Sql, and not neccessarily a Datacontrol. Set / open your recordset of choice (DAO, ADO, RDO, whatever) to: "SELECT * FROM TableName ORDER BY SURNAME ASC" -- Dag. > > Psychological block! I just need a push to get started > > Suppose I want to put an original record table in alphabetical order, [quoted text clipped - 21 lines] > > Using DAO or ADO ? The SQL you'd want to try is: SELECT [Surname] FROM Phonebook ORDER BY Asc; Try writing a query in your phonebook database that sorts by Surname first (Ascending or Descending). That way you can call the query that was already written, that may be faster for you. You also have to tell SQL which direction you want to sort (ORDER BY). GL! Gary >Psychological block! I just need a push to get started >Suppose I want to put an original record table in alphabetical order, [quoted text clipped - 15 lines] >Is there a website that'll tell me the very beginnings; not so much >the detail of sql; more how to slot it in to vb? I hate follow ups that don't answer my question but tell me how I should think. However, in this case... There is very little difference between using SQL in the VB environment and elsewhere. The mechanics of establishing a connection between a program and a database differ between various languages and operating systems. Once you have done that you are in "SQL land" and you need to think in terms of views and joins. I found Microsoft Access remarkably helpful in making the transition from data files to relational databases. The query design view provides a fairly intuitive way of writing a query and it can be converted to an SQL SELECT statement by clicking on the SQL view in the View menu. You can also test the SQL statements you use in your VB program by cutting them and pasting them into Access. Some advanced SQL statements are either not available or are very clumsy in Access but by the time you need them you will understand SQL well enough to not require the intermediate Access step. To avoid the shame I mentioned in my opening paragraph here's a connection to get you started. If you do use a DataControl (and Access) then set it's DatabaseName property to "c:\myproject\phonebook.mdb" and it's RecordSource property to "SELECT * FROM phonebook ORDER BY Surname". If the form contains a text box with a Datasource set to your DataControl and a DataField set to Surname then it should be displaying the first Surname in your database. >Thanks > >Tony Hi Thanks That works, and opens doors. I notice the SQL doesn't effect the order in the original table - If I open it later in Access, it is still in the original order. Is that the way SQL & Access works?. I guess that's ok. I've got a project up-and-running in Excel VBA This seems very different. cheers Tony >Hi >Thanks [quoted text clipped - 3 lines] >open it later in Access, it is still in the original order. >Is that the way SQL & Access works?. By definition, there is no "order" in a relational database. The order you see is the "view" you defined in your SQL query. Access protects you from needing to know this by always displaying a table using a default query. That query changes when you sort on a particular column. If you view the table using Visual Basic it is up to you to supply the query. >I guess that's ok. > >I've got a project up-and-running in Excel VBA >This seems very different. I'm afraid it is. Thinking in SQL is a significant departure from thinking in other languages. It's worth learning though because, in combination with the data bound controls in VB, it eliminates a lot of the tedious code associated with processing records in a database. Furthermore, your knowledge will be (almost) completely portable. Not even Microsoft have tried to undermine the standards. >cheers >Tony > Hi > Thanks [quoted text clipped - 10 lines] > cheers > Tony Think of SQL as a translator. If you don't understand the words, the translator lets you know what *in essence* the other person is saying, perhaps minus a few tidbits. SQL, like a regular Access query or a VBA sort on a Pivot table in Excel (should you write one) doesn't affect the sort order of the original because that's not the way the data was constructed. I mean- you can build a three level house and ladder up to the third floor to enter, but you still have to have the foundation built, plus the first and second floors. A regular Access query would sort the data the same way, but not effect the original Table's data. If you want to change the way Access displays the table data, you need to change the sort properties of the table itself. Select the column you want to sort by (say Surname in your case?) - Choose the A to Z button and then close the table, saying Yes to saving the layout. Then Access will save the sort in the table layout mode. Everytime you want to change the sort order you have to do that. So, as you can see the little bit of time you take to learn a few SQL commands will really pay off. There are only about 35 to 50 commands total in the language (I forget the exact #). They will make your database runs MILES faster (or retrieving the data will anyway) and it makes it also much More flexible for you - the Chief Programmer of your App. Pick up a SQL book at your library: it isn't as daunting as you'd think. I got a copy of SQL Server 2000 in 21 days and that was pretty much about as straightforward as you can get. I was writing SQL code in a matter of a day or two and feeling pretty confident with it. Gary Hi, I'm starting to get the hang of this (compared to a day ago anyway). I got a dbgrid control working too. I can see that there's no advantage in sorting the original, but curious whether I can do that with vb without coding it all myself. I'll do as you say & get a library book. I'm competent at XL Coming to Access for the first time, it seems a curious design; no "save as", no record macro... patience, I guess!.... tony > Hi, > I'm starting to get the hang of this (compared to a day ago anyway). [quoted text clipped - 10 lines] > > tony Some advice: Do not record Macros!! Access supports them but they run Much slower than VBA. If you want a form to open at startup and then do something, put the code in VBA and leave the 'macro' window blank. Trust me. I'm no expert w/Access by any means, nor with SQL, but my companies order processing software is an Access/SQL front and back end. My eventual goal is to convert the Access front-end to a VB front end and leave the data manipulation alone. If you have a table open, Save As is not an option. Close the table (or unselect it) and then go to File to Save As... I'm much better at Excel too, but I can't avoid Access and once you get the hang of it, it starts to make sense. Good books for Access (see if your library has them) are either: Mastering Access 2000 Programming by Alison Balter (has a good chapter - I think it's Chapter 7- on VBA) and forms/reports/tables/etc. I believe she tells you why Macros should be avoided too.... Access 2000 Fundementals (I forget the author - but it's a Microsoft Press book) for a thinner, more manageable introduction. Both these books cover SQL in the regard of how it applies to Access. They are great starters/reference guides. Access 2000 Power Programming if you decide you want to become a heavyweight. I got it and the 2nd chapter is on modifying the registry! Heavy stuff. I'm not quite that advanced! :-) If you perchance get stuck on an Access/SQL component- drop me a line (remove NOSPAM from address). If I don't know the answer - I can probably get someone who can. Also try the NG comp.lang.basic.visual.database for VB/db related Q's. Unfortunately I don't have too much exp. with using VB to front-end to Access (yet!). Good luck! Gary Gary I emailed you and it bounced. is the latter part, at vtiboston dot etc correct? I'm tonyjeffs at tonyjeffs dot(followed by "com") If you send me a short emai, I'll have your correct address & reply promtly. cheers Tony |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.