|
Hi All,
I have one Excel sheet "Student.xls", which has different worksheet as "Sheet1","Sheet2"..like that.
Consider now "Sheet1"; it has 3 columns - "Name", "Age", "Sex". All these columns has row entries.
Now I have one access database, "alldata.mdb". It has One table "Record" which has 3 fields as same as column name of Excel sheet-- "Name", "Age", "Sex".
I want to populate this access database with the entries of Excel sheet from "Sheet1".
Can someone let me know how to do this?
I tried this:
// first connected xl datasheet:
Dim filename As String = "C:\Student.xls"
Dim con As String
con = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename & ";" & "Extended Properties=Excel 8.0;"
Dim connection As OleDbConnection = New OleDbConnection(con)
connection.Open()
The above Excel connection was succesful, but how to import the data from this excel sheet into the access database.
Please suggest.
Thanks,
R.S.
|
|
|
|
|
Select * FROM [Sheet1$]
or however your query looks like.
Enter the data into a dataset. Open a new connection to the alldata.mdb and copy the data from the dataset.
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Hi,
I have copied the data in a new dataset "ds1". Please tell me how to copy this "ds1" contents into Access Database. I am trying with following:
Dim da2 As New OleDbDataAdapter
Dim conn As OleDbConnection
Dim cmd As OleDbCommand = New OleDbCommand()
conn = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\alldata.mdb;")
cmd = New OleDbCommand("SELECT * FROM Denver", con)
da2 = New OleDbDataAdapter(cmd)
da1.Fill(ds1, "Denver")
But not able to populate the excel sheet.
Can You guide me how to do this??
Thanks,
R.S.
|
|
|
|
|
Sorry By mistake.... I made the statement
"But not able to populate the excel sheet."
What i was:
"But Not able to populate the Access Database".
Thanks
R.S.
|
|
|
|
|
hi dearest brothers/sisters
i have created trigger in MSSQL SERVER.
CREATE TRIGGER IDx on Table2 after INSERT
AS
insert into Table2 (RefNo) select RefNo from Table1
i need Table2 Foreign key RefNo to be filled no of times which means
eg. table2 may need RefNo to be filled in three rows.
currently i get single row others kept NULL values
help me plz
thanks
so much of happy ending...
|
|
|
|
|
|
Thanks Mohsiul Haque . its really helpful and learned some.
so much of happy ending...
|
|
|
|
|
Triggers are EVIL. Although this is a valid use of a trigger I would suggest using a normal stored proc and surrounding the inserts with a transaction.
Begin Tran
insert parent record
get scope_identity for the inserted record
begin a loop
insert the number of records in the child tables
end the loop
commit the transaction
return the id to the calling method
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks to Mycroft Holmes . its really helpful !
so much of happy ending...
|
|
|
|
|
A query that used to take milliseconds is now taking 3 seconds to run
The table only has 7000 records and I rebuilt the one index that it uses
I am thinking if I drop/create the table the problem may get fixed, but the table has several dependent views, etc. Will each dependent item need to be created again?
Thanx,
>>>-----> MikeO
|
|
|
|
|
|
In MS SQL Server Management Studio,
1) create a new query
2) paste the query in the query window
3) under the Query menu select the option to "Include Actual Execution Plan"
4) Execute the query.
Check out the execution plan results and you want to avoid "scans", this means that it is reading the table or index sequentially.
Also under the Query menu, choose the "Include Client Statistics" and check those values for anything that may seem out-of-whack.
Pleasse reply to this posting so that I know if this was helpful.
david
|
|
|
|
|
I followed the steps in your reply. Nothing looks to be wrong with the query. It even appears to execute normally in Management Studio. When I execute it in the code on the client though it takes about three seconds.
The client app has been running successfully for about four years. Now it is getting bogged down by two queries. Both do field sums. Any ideas what could change on a server or connection that would slow down a SUM operation?
This is a close approximation of the query that is taking so long:
SELECT SUM(WorkSecs) as WorkTime, SUM(RunSecs) as RunTime
FROM tblData WHERE Machine = 1 and Operator = 1
Thanx,
>>>-----> MikeO
|
|
|
|
|
I need to know how to archive data from particular database when it is growing above 1GB.Also it has to be restored when it was needed.Any Idea
|
|
|
|
|
For most of the time Archiving database can be done by using SQL Jobs, I think at first you need to create a stored procedure which will fetch the data and dump into the archive database and then just call it from SQL Jobs with a given schedule.
|
|
|
|
|
There's no "archiving" in SQL Server, there's only "backup" and "replication".
|
|
|
|
|
You are asking for a strategy to manage your data, you are not going to get a satisfactory answer in a forum post.
Most archiving is done by time stricture, anything that is 2 yo move to the archive database. This type of process requires that you copy (replicate) the data into an identical database and delete it from the production DB. Here you run up against data structure issues, all your chages to production structure needs to be reflected in the archive data.
Queries can be written across both databases, but these are specific to archived results.
Another strategy is data warehousing your data. You need to do some serious research into your business requirements before deciding on an archiving strategy.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
hi all
i have a table with the PrimaryKey of AutoNumber
so i want to insert a row and retrieve it`s PrimaryKey !!!
by the way my DBMS is MSAccess 2003
can any one help me about this?
|
|
|
|
|
You can't really make the insert query return anything else then the number of affected rows.
You will have to follow your insert statement with a select statement.
Something like:
INSERT INTO Table1 (Field1) VALUES('blablabla')
SELECT MAX(PrimaryKeyField) FROM Table1
My advice is free, and you may get what you paid for.
|
|
|
|
|
No, no no. Really that is about the worse way you can retrieve the inserted key.
|
|
|
|
|
sometimes several people, several apps are operating on the same database table. At the same time.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read formatted code with indentation, so please use PRE tags for code snippets.
I'm not participating in frackin' Q&A, so if you want my opinion, ask away in a real forum (or on my profile page).
|
|
|
|
|
Well, they shouldn't. There should be a law against it.
My advice is free, and you may get what you paid for.
|
|
|
|
|
INSERT INTO target [IN externaldatabase] [(field1[, field2[, ...]])] SELECT [source.]field1[, field2[, ...] FROM tableexpression;
you can first insert and then can retrive scalar value like below
select max(ColumnName1) as Max_RowNumber from table_name;
I guess this will help you.
Reasons are not Important but Results are Important.
http://www.sql4professional.blogspot.com
Swati Tripathi
|
|
|
|
|
Second response without a clue.
|
|
|
|
|
Please ignore both posts above telling you to select the max(field) after your insert. It will be prone to problems if you continue to use this method in a high-usage environment.
MSAccess has a built-in variable for this - @@IDENTITY. For SQL Server it is slightly safet to use the function SCOPE_IDENTITY().
So this is what you're after:
-- Assumes "MyTable" has an Autonumber PK field
INSERT INTO MyTable(Foo)
VALUES('Foo')
SELECT @@IDENTITY AS InsertedKey
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
