Database

DAO Access and system.mdw

6-Apr-04 21:35

this :
http://www.codeproject.com/useritems/databinding.asp[^]
is a very beginner article.
you can start with it
also check www.vbcode.com[^]

cr976-Apr-04 3:56

cr97

6-Apr-04 3:56

How can i open an access-file (database.mdb) with DAO using a special systemdb/workgroupfile (database_sys.mdw) ???

if i use ODBC i can defien a systemdb in de ODBC-connect-string ... but it doesn't work with DAO !!!

thx a lot

cr97

Mike Ellison6-Apr-04 5:45

Mike Ellison

6-Apr-04 5:45

Hi cr97. Take a look at the following article on MSDN; the sample code you want is under the heading "Opening a Database with User-Level Security".

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoadoupdate_topic4.asp[^]

cr976-Apr-04 21:47

cr97

6-Apr-04 21:47

thanx ...

But how can i do that in C++ ???

i found this ...

void CDaoWorkspace::Create(LPCTSTR lpszName, LPCTSTR lpszUserName, LPCTSTR lpszPassword)
{
  ...

	// Get the DAODBEngine interface and initialize if necessary
	_AFX_DAO_STATE* pDaoState = AfxGetDaoState();
	if (pDaoState->m_pDAODBEngine == NULL)
		InitializeEngine();
  ...
}

and i know that DAODBEngine has the method ...

pDaoState->m_pDAODBEngine->put_SystemDB(THIS_BSTR SystemDBPath);

Can i call this like it is to change the SystemDB ???
And i can't use this AfxGetDaoState() ... it's unknown ...

mhg cr97

Mike Ellison7-Apr-04 5:28

Mike Ellison

7-Apr-04 5:28

Would this article help?

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/vclib/html/_mfcnotes_tn054.asp[^]

Multiple table select queries in mySQL

cr977-Apr-04 21:15

cr97

7-Apr-04 21:15

Yes i think it would help !!!

I found this article yesterday ...

thank you !!!

cr97

docdeath6-Apr-04 2:38

docdeath

6-Apr-04 2:38

Could someone help me construct an SQL query which will conditionally select records from multiple tables which share the same schema, without joining every compatible record onto the end of each (creating a total mess) like "select * from <table1>, <table2> where 1" does. Placing the name of the table as a field for each would be a bonus (Im using mySQL)

The database I have is quite unusual as it contains a lot of binary-serialised data which has been categorised into a lot of different tables (for instance one table for records beginning with A, B, C etc), for (I expect) high-performance and easier management. If there is any other tweak you can think of that'll allow better querying on a collection of similar tables, your input would be welcome.

How to join on multiple records?

matthias s.6-Apr-04 1:27

matthias s.

6-Apr-04 1:27

Hello,

I've got a table called samples. This table has three fields called PicID1, PicID2 and PicID3 for simplicity. My second table is called Pics. Pics has an ID field (key) and a PicName field. The select query I'd like to write selects all records from the Samples table and the PicName value for each of the PicIDs (1,2 and 3):

[Samples]
ID|PicID1|PicID2|PicID3
1|345|664|733

[Pics]
ID|PicName
..
345|Himalaya
664|Sahara
733|New York

The result should return a recordset containing all records from the samples table and "instead of the numeric PicID*" the Pics.PicName.

Can this be done in one query? If so, how?

Thanks in advance,

Matthias

If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)

www.emvoid.de

Mike Dimmick6-Apr-04 1:42

6-Apr-04 1:42

SELECT Samples.ID, P1.PicName AS PicName1, P2.PicName AS PicName2, P3.PicName AS PicName3
FROM
   Samples
INNER JOIN
   Pics P1 ON Samples.PicID1 = P1.ID
INNER JOIN
   Pics P2 ON Samples.PicID2 = P2.ID
INNER JOIN
   Pics P3 ON Samples.PicID3 = P3.ID
-- insert WHERE clause here

Basically, all you have to do is to alias the repeated table name.

Stability. What an interesting concept. -- Chris Maunder

matthias s.6-Apr-04 2:05

matthias s.

6-Apr-04 2:05

Thanks, that's exactly what I was looking for!

If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)

www.emvoid.de

Hesham Amin6-Apr-04 1:49

6-Apr-04 1:49

hi use subqueries.

Select Samples.ID
,(Select Pics.PicName where ID=Samples.PicID1) as FirstPic
,(Select Pics.PicName where ID=Samples.PicID2) as SecondPic 
,(Select Pics.PicName where ID=Samples.PicID3) as ThirdPic

but i prefere that you re-design the database and create a link table that holds the SampleId and the PicID

Table:Sample_Pics
SampleID | PicID
1 |1
1 |2
1 |3
2 |4
------------------
etc.
this will allow you to have a variable number of pics per sample not just 3.

SQL Server Procedure Output

matthias s.6-Apr-04 2:07

matthias s.

6-Apr-04 2:07

I've been thinking about this, but if fact, there will never ever be a situation when there is more then three Smile | :)

Thanks anyway,

Matthias

If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)

www.emvoid.de

SamAmman5-Apr-04 23:06

SamAmman

5-Apr-04 23:06

Hello everybody ...
Well, I have a problem in something with SQL Server 2000 ...
I want to write a procedure in stored procedure in SQL Server 2000, this stored procedure should output a variable holding the number of records in the table.

ex
select count(*) from tbl
where tblAccount = 2828;

I want this procedure to output the number of records retrieved, and i want to get it from ASP page. Actually i used a command object in ASP to execute my procedure, and with a recordset.

rs.open cmd

but how can i get the output from this procedure???? and how can i write like this procedure in SQL Server 2000 ????

Best Regards
Sam

Sam2004

Re: SQL Server Procedure Output

Mike Dimmick6-Apr-04 1:26

File space used in SQL2000

6-Apr-04 1:26

If you've used SELECT and opened a Recordset, the results will be in the Recordset object. The recordset has the concept of the current row - the Value property of the Field objects in the Recordset's Fields collection will contain the retrieved value of that field in the current row.

Straight after opening, if any rows were retrieved, the Recordset object's current row will be the first row returned. If no rows were retrieved, the EOF property will be False.

Since you haven't given the field a name in the result set, you will only be able to access it by position, i.e. as rs.Fields(0). To give it a name, use

SELECT COUNT(*) AS RowCount
FROM tbl
WHERE tblAccount = 2828;

As for the procedure, the easiest way is to send the SQL explicitly to the server, by setting the command object's CommandText property. This isn't the best performing method, though; look up the documentation on stored procedures and parameters.

Stability. What an interesting concept. -- Chris Maunder

Hesham Amin5-Apr-04 1:34

Re: File space used in SQL2000

5-Apr-04 1:34

Hi how to know the sapce used in the data file or log files ?
the sp_spaceused stored proc gets the space used by a database or object.But what i need is the space used by in each data or log file.
how to get this info ?

Mazdak5-Apr-04 21:20

Mazdak

5-Apr-04 21:20

You can use your programming language to get size of your database and its log file.If its C# you can use FileInfo.Length and if it is C++ you can use GetFileSize().

Mazy

"I think that only daring speculation can lead us further and not accumulation of facts." - Albert Einstein

Re: File space used in SQL2000

Hesham Amin5-Apr-04 22:12

Re: File space used in SQL2000

5-Apr-04 22:12

thank you .. but seems that my question wasn't clear enough.
I can get the file size (OS file size) using sp_helpFile. But this is the total space allocated for the file..
What i need is to know how much actual space consumed by data or log within the file..
for example you can allocate a 50MB file while the data actually use 20MB. In this case i want to get 30MB.

Mike Dimmick6-Apr-04 1:35

Reducing the size of the database files

6-Apr-04 1:35

USE database
EXEC sp_spaceused

I can't find a way of specifying which database you want to look at, other than just selecting the database with USE.

Stability. What an interesting concept. -- Chris Maunder

Colin Angus Mackay4-Apr-04 23:26

Colin Angus Mackay

4-Apr-04 23:26

Hello,

DB Admin is not one of my stong points so I'm a bit stuck. I have a development database that has almost filled the disk. I want to reduce the file sizes so I can continue development (a process I left running over the weekend has increased the log file by 10Gb - It didn't work so I have to fix and rerun it and I only have 5Gb left)

Can anyone let me know how to reduce the file sizes back to something sensible (the operation I was running modified about 17% of the records and deleted 83% of the records it was working on). I tried Shrink Database but it reports that it has shrunk the database by a few hundred Mb but the files are physically the same size. Confused | :confused:

Any help would be much appreciated.

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

"On two occasions, I have been asked [by members of Parliament], 'Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?' I am not able to rightly apprehend the kind of confusion of ideas that could provoke such a question."
--Charles Babbage (1791-1871)

Re: Reducing the size of the database files

Mike Dimmick5-Apr-04 1:02