|
The basic SQL syntax (select, insert, etc) is the same for all SQL databases. They start to differ in the following areas:
o Functions that can be used within SQL expressions (e.g. IIF).
o Outer joins.
o Available data-types.
o Whether stored-procedures and triggers are available.
o Security.
I tend to use the MS-Access query-painter to get the basic SQL syntax, then switch over to the SQL-Syntax view for the nitty-gritty stuff. The online help provides details of the available functions (if you look hard enough).
Regards
Andy
PS: If you start to use MS-Access in a production networked environment then make sure you have a copy of the JETCOMP.EXE utility available (from the MS support website). If your MDB database file get corrupted then JetComp may save your bacon.
PPS: If you want an alternative then the MSDE database engine is included on the MS-Access installation CD (you have to install it manually). This is a cut-down version of the SQL-Server product (so you should be able to use the .Net SQL-Server database classes).
|
|
|
|
|
You've used the term "MS-Access query-painter" a couple of times. Is this something different from the Design View of a query? I've used the expression builder a couple of times too, which is somewhat helpful. I just thought it would be nicer to have a decent reference. Seems like most of the books on the subject are more about the basic functionality available and less about the details of the SQL syntax that differs from other DBs.
As far as using this in a production environment, there's just no way. I don't trust Access. The company that built the application that generates and uses this DB does use access, but they provide no queries for reporting. I just decided that rather than maintaining two sets of data (the original in Access and then copying it to SQL Server), I would just learn how to use the query tool on Access. I will eventually be automating the generation of reporting (charting more than anything) in Excel with Office Automation. I guess I would call learning Access a necessary evil.
Thanks again.
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
You're right - I ment the "Design View" and "SQL View" for Queries. I didn't have Access open when I wrote my previous email.
Andy
|
|
|
|
|
Hi all,
I am new in ASP and ADO
when I call recordset.open "select * from db where ID='aa'"
the returned record set seems wrong sometimes. i can not fetch the fields data. if i use this "rs.fields("PIN")" it gives an execption
sometimes it 's fine.
why?
|
|
|
|
|
Hi,
I was wondering whether it is possible to register for a DataTable events from a remote machine.
Lets say i have machine A , which has a DataSet , and let say also that i have machine B, which has a copy ( clone or cluster , i don’t know the right term for it ) and what I’m looking for is a way to know when something in the DataTable in machine A is occurred so i would update the DataTable in machine B.
I thought of registering machine B for the DadaTable events in machine A but i don't know how.
Do you think on a better solution for my problem?
Amir Harel
Why we always search for the generic solution for a specific problem...
|
|
|
|
|
I have trouble concatenating a SQL string and have it executed on the fly. The following is the code fragment extracted from inside a stored procedure:
...
... stored procedure ...
...
DECLARE @temp_table_name VARCHAR(50)
DECLARE @temp_table_sysname sysname
DECLARE @token VARCHAR(100)
SET @temp_table_name= '##SPVA12A4ACA2EBA74EC2B5856687E0FF42A8'
SET @temp_table_sysname = CAST( @temp_table_name AS sysname)
--OPTION 1: Does NOT work, error: "Must declare the variable '@temp_table_sysname'."
SELECT @token=strToken FROM @temp_table_sysname WHERE rowindex=1
--OPTION 2: Does NOT work, error: "Must declare the variable '@temp_table_name'."
SELECT @token=strToken FROM @temp_table_name WHERE rowindex=1
...
... stored procedure
...
NOTE:
1. I am aware of sp_executesql or EXEC(strSQL). But I need something that returns me @token.
2. I have to do this in stored procedure.. would avoid doing it in ASP - to avoid the messy scripts. Stored Procedure is more modular and framework offers more security (example: backup)...etc.
Hope to hear from you soon - I'm really stuck because of this and another ASP.NET problem.
norm
|
|
|
|
|
1)What must contain a ConnectionString?
2)How change a color in Excel table(cell, text) via VC++?
|
|
|
|
|
I don't have my development machine booted at the moment, but you may find the information on the following web-page useful:
http://support.microsoft.com/default.aspx?scid=KB;en-us;q278973[^]
It describes how to use the MS-Access jet OLE-DB database driver to access an Excel spreadsheet.
Note that it is not possible to change the colour of a spreadsheet cell via an OLE-DB driver. You would have to resort to OLE-Automation of the Excel application.
Hope this helps.
Andy
|
|
|
|
|
Hiya have just started to learn SQL and am having trouble with the INSERT and DELETE and EXECUTESQL statements. Can someone show me how to use these functions?? have tried but keep getting it wrong.
What I really need is a example program with some SQL statements being used.
I am using CDatabase and CRecordset for the database.
Thanks for your help.
grahamoj.
|
|
|
|
|
|
Thanks Alexandre, going to check those out now.
grahamoj.
|
|
|
|
|
I am trying to create a relational database for a product catalog. The problem is that there are two similar product types that have most of the same information but one product has two more fields of information than the other. What is the standard way to handle this situation? Do I create two seperate tables?
For example, let's say you have a catalog of books. But there are print books and e-books. Then let's say that the two book types have the same information except that the e-book also has a field for the number of downloads. Do you create a book table and an e-book table? This would seem to me to be extra work since the two book types only differ by one field. What do you think?
Thanks.
Mark Sanders
sanderssolutions.com
|
|
|
|
|
Mark Sanders wrote:
Do you create a book table and an e-book table?
Yes.
This would seem to me to be extra work since the two book types only differ by one field.
Still, does a "download count" have anything to do with the paper version? Of course not, hence it has nothing to do in the "book" table.
Possibly you'd have even three tables. One describing the "book", one describing the proprties of the paper version and one describing the properties of the "electronic only" version.
++luck;
|
|
|
|
|
Can you give me a little more info on modeling the three tables? Would a record in the "e-book properties" table have a FK which relates to the PK of the "book" table?
Mark Sanders
sanderssolutions.com
|
|
|
|
|
Mark Sanders wrote:
Would a record in the "e-book properties" table have a FK which relates to the PK of the "book" table?
Yep, that would have been my choice.
|
|
|
|
|
Since I would only want one record in the properties table to be associated with one record in the book table would the bookID actually be both a FK and a PK?
Mark Sanders
sanderssolutions.com
|
|
|
|
|
-= Lazy way =-
Since it is only one field, you can just have a nullable field for the number of downloads, and then add a constraint to validate that the download field is null if it is not an e-book.
-=Better way=-
But if you have many properties of a book, and many properties of an e-book some of which are the same,most of which are not. You may want to consider another table to hold these poperties, with a schema in the format: BookPK, property, value
-=Best way=-
Have three tables ... properties of paper books, properties of e-books, and a list of books
|
|
|
|
|
I think you would get different answers from different people. My personal tack on it is to keep all book data in a single table. Then you let your stored procedures or business rules layer handle figuring things out.
I would just create one table for all books and then create a second table that contained the book id and its download count. Every book won't have a download count, but that's ok. You let your app or stored proc figure that out. You could even keep a lookup of book types and a table of book IDs with their book type. Just do a join on the two tables to get all books of that type. That type will tell your app whether you should do a download count look up. This way, if you ever added a new book type, you don't have to add a new specific book table just to accomodate one or two new parameters. You already have one that suits your needs. You just create new tables with the book ID as the lookup mechanism.
This is just one guy's opinion on this. Take it or leave it. 
-Matt
------------------------------------------
The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall
|
|
|
|
|
What's wrong with this simple statement:
DECLARE @strSQL nvarchar(1000)
SET @strSQL='SELECT * FROM ##SPVA7D25F0176A1C4B4F9F7F979B7867744E WHERE rowindex=1'
EXEC @strSQL
When I execute this under Query Analyzer, I got:
"Server: Msg 2812, Level 16, State 62, Line 3
Could not find stored procedure 'SELECT * FROM ##SPVA7D25F0176A1C4B4F9F7F979B7867744E WHERE rowindex=1'."
Thanks
norm
|
|
|
|
|
I run it from Query Analyzer and get the same error,but if you put this statement in stored procedure it will work.I don't know why.
Mazy
"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits
|
|
|
|
|
It is in fact part of a stored procedure. I took it out to make it easier for others to examine the cause of my agony.
norm
|
|
|
|
|
Typically, whenever I get a 'could not find' kind of error message, I suspect a permissions problem. Have you been granted execute permission for this stored procedure?
Chris Meech
"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.
"AAAAAAAAAHHHHHH!!!!! Those leaks are driving me crazy! How does one finds a memory leak in a garbage collected environment ??! Daniel Turini Nov. 2, 2002.
|
|
|
|
|
it's a raw SQL string. It's no stored procedure, if you'd read my post again.
the error msg is very confusing.
norm
|
|
|
|
|
Use sp_executesql.
The Execute command is for stored procedures.
Julio-SESICO
|
|
|
|
|
Try
EXEC ( @strSQL ) (note the parentheses)
or
sp_executesql @strSQL
Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
