Database

perlmunger12-Feb-03 2:54

12-Feb-03 2:54

I'm actually toying with what database I should use. I was given the data in Access, though, so I'm starting there. The query functionality is not very powerful, so I don't think it has the functionality you describe. I will most likely end up moving this over to SQL Server, but I am trying to get something quick and dirty in the near term by just using what Access offers. Oy!!

Thanks for your help.

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

andyharman12-Feb-03 4:27

12-Feb-03 4:27

Hi Matt

I got the follow statement to run through the MS-Access query painter:

SELECT DateValue(rentaldate) AS RentalDay,<br />
   COUNT(*) AS TotalRentals,<br />
   sum(IIf(rentaltype='VHS',1,0)) AS VhsRentals,<br />
   sum(IIf(rentaltype='DVD',1,0)) AS DvdRentals<br />
FROM Rentals<br />
GROUP BY DateValue(rentaldate);

Andy

perlmunger12-Feb-03 6:03

12-Feb-03 6:03

You are the man!!! Thank you. This is perfect!! I really appreciate it. By the way, do you know of any good source for SQL syntax in Access? I've found a few tutorial sites, but can't seem to find more of a guide on syntax and the differences between SQL Server SQL, ANSI SQL, and Access SQL. Any ideas?

Thanks again.

-Matt

------------------------------------------

The 3 great virtues of a programmer:
Laziness, Impatience, and Hubris.
--Larry Wall

andyharman12-Feb-03 23:51

12-Feb-03 23:51

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).

perlmunger13-Feb-03 5:18

13-Feb-03 5:18

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

andyharman13-Feb-03 6:18

exception when call (recordset.open "select * from db where ID='aa'")

13-Feb-03 6:18

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

rxl11-Feb-03 5:57

rxl

11-Feb-03 5:57

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?

Remote DataSet

Amir Harel10-Feb-03 22:13

Amir Harel

10-Feb-03 22:13

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...

trouble concatenating a SQL string that returns @token on-the-fly.

devvvy10-Feb-03 21:39

devvvy

10-Feb-03 21:39

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

How connect ADOX to Excel?

Anonymous10-Feb-03 18:14

Anonymous

10-Feb-03 18:14

1)What must contain a ConnectionString?
2)How change a color in Excel table(cell, text) via VC++?

Re: How connect ADOX to Excel?

andyharman12-Feb-03 4:45

Can anyone give me an example of these SQL statements??

12-Feb-03 4:45

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

IrishSonic10-Feb-03 12:01

IrishSonic

10-Feb-03 12:01

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.

Re: Can anyone give me an example of these SQL statements??

Alexander Kojevnikov11-Feb-03 6:42

Alexander Kojevnikov

11-Feb-03 6:42

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/acdata/ac_8_md_03_1kz8.asp[^]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_de-dz_9lut.asp[^]
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_ea-ez_2h7w.asp[^]

All BOL articles have code samples.

HTH.

Alexandre Kojevnikov
MCP (SQL2K)
Leuven, Belgium

Re: Thanks Alexandre

IrishSonic12-Feb-03 10:16

IrishSonic

12-Feb-03 10:16

Thanks Alexandre, going to check those out now.

grahamoj.

Database Design

Mark Sanders10-Feb-03 11:52

Mark Sanders

10-Feb-03 11:52

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

Mike Nordell10-Feb-03 12:42

Mike Nordell

10-Feb-03 12:42

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;

Mark Sanders11-Feb-03 4:19

Mark Sanders

11-Feb-03 4:19

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

Mike Nordell11-Feb-03 6:09

Mike Nordell

11-Feb-03 6:09

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.

Mark Sanders11-Feb-03 8:57

Mark Sanders

11-Feb-03 8:57

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

Jason McBurney10-Feb-03 13:02

Jason McBurney

10-Feb-03 13:02

-= 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

perlmunger11-Feb-03 18:39