|
yeah sure ... nothing expected ... most how to export from DB to excel .... and the next giving how to create new excel .
some uses: DoCmd.TransferSpreadsheet which is usless for me, since i need to idendity the excel sheet as an abject and to do some calculations on records before dumping it in the table .
0 will always beats the 1.
|
|
|
|
|
This[^] should help to get you started on reading from Excel.
I must get a clever new signature for 2011.
|
|
|
|
|
Thanks Richard, but am not familiar with C# honestly, ... also , reading from excel that i create , i think is bit easy , but my excel sheet is alreacy exist , all i need is to - if i can say - pass this particular excel sheet to a function, in whcih it will read and manipulate.
Any article for same topic in VBA ????
Regards,
0 will always beats the 1.
|
|
|
|
|
scorp_scorp wrote: Any article for same topic in VBA
I don't know of any, or whether it is possible to write to Access from VBA. Try a Google search and see what you can find; I know there are a number of specialist Excel technical forums around.
I must get a clever new signature for 2011.
|
|
|
|
|
You're making things difficult for yourself. I simply use ADO.net.
There is a .net connector for both Excel and Access -- I make a connection to each, query from one, and write to the other. Slick as snot.
|
|
|
|
|
Am not that good in coding, but, PIEBALDconsult wrote: I simply use ADO.net.
suggest that am using an interface coding application or language (like .net), but am using VBA.
does the ".net connector " exist in VBA ?? is there such a connector in VBA ???
Regards.
0 will always beats the 1.
|
|
|
|
|
Well, then this would be an excellent opportunity to improve yourself. Yes, I recommend using C# to write a simple command-line utility to do it. I wrote one just a few weeks ago to do just such a thing. There's really not much to it; the tricky part is in making it flexible so you can easily use it for other similar tasks in the future, but you can forego that for now.
|
|
|
|
|
Thanks for the support PIEBALDconsult...
But am now in middle of this application (Access DB, VBA, forms) am not sure that frezzing it at this stage and jump to starting it again (writting the interface) using C# . time is a bit tight...  may be when i finish it, ill re-do it again in C# so i can build some skills using C#.
0 will always beats the 1.
|
|
|
|
|
I came across the same problem and I wrote up a little VBA code in ACCESS handle this. It is still in the beta phase and I am still pretty week in the code formating and documenting code, but the functionality is all there. So far it has the following features:
- File Folder Scanning and Keyword Search
- Batch excel file scanning
- Semi-Automatic ACCESS database creater from EXCEL.
- Data Viewer with column order, and sizing
- ACCESS data filter
- Export to excel based on the ACCESS data filter.
Again it is in the beta phase. Email me at david.lario@ualberta.net if you want me to forward what I have to you. If there is enough interest I could try post the routine to the code project.
|
|
|
|
|
hi , i would like to fire set of queries when a select query is fired on to particular table, can any one help in this ..
|
|
|
|
|
As far as I know, this can't be done with triggers if that is what you are thinking.
Why don't you create a stored procedure which does your "queries" and have the applications use that stored procedure instead of directly accessing the table ?
Just a thought.
|
|
|
|
|
Triggers are valid for events, select is not considered an event. Besides triggers are EVIL  .
I'm curious as to the requirement for a trigger on a select, why do you need it
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I have queries regarding Database design in sql server 2005.
I have Book Library. I have a master where i am storing different category of writers for eg. Story Writes, Poets, Technical writers, Fiction Writes etc.
For eg. I have stored Louies David who is a Story writer as well as Poet.
I want to develop a search for above category for selected name or populate a list box for single category.
I have also another processes like contract for Books which is in transit but need to store details in Database. for eg Book Contract. It is having status as 'Approved' or 'Transit'. But i Need to store the categories and name of the writes in the database as well.
I have created Writes Category Table with Metadata for eg,
1) Table - WriterCategory
WriterCatID int - not Null
WriteCategory - int - metadata value ( Binary number for eg. 2) then for next record it will be 4, next will be 16 .. as so on.
CategoryDescrition - Varchar(50) - for . eg. Story Writer
2) Table - WriterMaster
WriterMasterID - int
WriterName - varchar(50) for eg, Louies David
Metadata Total - metadata which is selected by user for eg. He is Storywriter + Poet then 2 + 4 = 6 will be stored in this table
3) WriterMasterDetail
WriterMasterDetailID - Int
WriterMasterID - FK of WriterMaster
Metadata - individual category Number stored in WriterCategory
From above Table i can populate list boxes for categories as well as from Name i can find the category of the selected name.
But when I want to this to be also get values for BooKContract then I should i stored the value. Should i go with same Masterdetails Table ?
The above design was to reduce table for each categories and redunant data. I want to maintain the same thorugh out the application ? I will have to store each CategoryID with WriterMasterID for each contract and will have to retrive with query which gives with multiple records along with metadata. I realised that i will have to first find the distinct name and then iterate for type of metadata to store BookContract.
Can u please suggest ? Do I need to stored each WriterMasterID for each category as column in table? Please suggest better solution.
Thanks in Advance.
|
|
|
|
|
meerabhuva wrote: The above design was to reduce table for each categories and redunant data.
Apply normalization techniques, and you'll get the correct design.
meerabhuva wrote: WriteCategory - int - metadata value ( Binary number for eg. 2) then for next record it will be 4, next will be 16 .. as so on.
Why did you skip 1 and 8? Your datatype says integer, how many items could you store in there? It looks like a running value btw, are you sure you don't want an Identity?
meerabhuva wrote: Metadata Total - metadata which is selected by user for eg. He is Storywriter + Poet then 2 + 4 = 6 will be stored in this table
Table: WriterKind
Id Caption
1 Storywriter
2 Poet
3 Scriptwriter
Table: WriterMaster
Id Name WriterKind
1 Berton Braley 2
It might be a flagged-enum in your code, but that doesn't make it a good idea to store it that way in a relational database.
I are Troll 
|
|
|
|
|
meerabhuva wrote: WriteCategory - int - metadata value ( Binary number for eg. 2)
That's one way, but it will limit you to thirty-two categories. Better to have a Category table and allow many-to-many relationships with Books and Authors.
Category
ID Description
1 Mystery
2 Romance
3 Sci-Fi
Author
ID Name
1 Douglas Adams
AuthorCategory
AuthorID CategoryID
1 3 <-- Douglas Adams + Sci-Fi
Book
ID Title
1 The HitchHikers...
BookCategory
BookID CategoryID
1 3 <-- The HitchHikers... + Sci-Fi
|
|
|
|
|
Here is my SQL
select
ID as [Emp ID],
Name as [Employee name],
Sal as [Salary]
from Emp
FOR XML AUTO, ELEMENTS, ROOT('customers')
When I issue this SQL then unknown data is added in xml with my field name.
I got the xml like
<customers>
<Emp>
<Emp_x0020_ID>1</Emp_x0020_ID>
<Employee_x0020_name>bob</Employee_x0020_name>
<Salary>2020</Salary>
</Emp>
<Emp>
<Emp_x0020_ID>2</Emp_x0020_ID>
<Employee_x0020_name>keith</Employee_x0020_name>
<Salary>6500</Salary>
</Emp>
<Emp>
<Emp_x0020_ID>3</Emp_x0020_ID>
<Employee_x0020_name>markc</Employee_x0020_name>
<Salary>5400</Salary>
</Emp>
</customers>
It shows <Emp_x0020_ID> instead of this <Emp ID>
because I mention column alias with space like "Emp ID". how could
modify my SQL as a result space will not be ignore I mean my xml would
<customers>
<Emp>
<Emp ID>1</Emp ID>
<Employee name>bob</Employee name>
<Salary>2020</Salary>
</Emp>
</customers>
please help me......urgent.
look like
tbhattacharjee
|
|
|
|
|
Tridip Bhattacharjee wrote: please help me......urgent.
I don't see anything urgent here.
However, if you read the XML specification you may notice that node names may not contain spaces, so your Emp ID label has been manipulated to make it comply with the rules.
I must get a clever new signature for 2011.
|
|
|
|
|
declare @SQL nvarchar(100) set @SQL = ''
select @SQL = @SQL + FieldName + ' as [' + Description + '], ' from FieldsInfo where TableName = 'Emp'
set @SQL = 'select '+ left(@SQL, len(@SQL)-1) + ' from Emp FOR XML AUTO, ELEMENTS, ROOT(''customers'')'
exec sp_executesql @SQL
above is dynamic sql and when it is executed by sp_executesql then i got output as as xml. if i want to store that xml in a variable. so then what i need to add in my tsql script....please help
tbhattacharjee
|
|
|
|
|
You would normally use a dynamic code like this for interface with a front end like .NET.
What you need to do is add INTO mytable before the FROM or define a table and use INSERT INTO mytable.
|
|
|
|
|
Replace "exec sp_executesql @SQL" with:
declare @xml xml
set @SQL = 'set @xml =('+@SQL+')' -- stores query result to @xml variable
exec sp_executesql @SQL,N'@SQL nvarchar(4000),@xml xml OUTPUT',@SQL,@xml OUTPUT
--- display result
print convert(varchar(MAX),@xml)
|
|
|
|
|
suppose i have two tables in sql server one is Emp and another is FieldsInfo. both table structure look like below
CREATE TABLE [dbo].[Emp](
[ID] [int] NOT NULL,
[EmpName] [varchar](50) NULL,
[Sal] [nchar](10) NULL,
)
CREATE TABLE [dbo].[FieldsInfo](
[TableName] [varchar](max) NULL,
[FieldName] [varchar](max) NULL,
[Description] [varchar](max) NULL
)
now i want to join both the table such way that sql should show data from emp table but field name will be shown from FieldsInfo table according to the relation. i mean Employee ID will be shown as Field name instead of ID and Salary will be shown as field name instead of Sal but value will be just like emp table. i am not being able to generate this type of output after joining. so please help with right sql script.
thanks
tbhattacharjee
|
|
|
|
|
|
I think Pvot will help you...
HELLO GUY
|
|
|
|
|
|
Dear All,
I need to create a procedure that creates an outlook task for a set of domain users when some condition in the data base is fired.
Is this possible?
I am using SQL Server 2008 R2, Exchange Outlook 2010
Regards
Kind Regards
OBarahmeh
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
