|
| |
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
 |
Update access Table from an Excel table in ASP - 4/30/2008 16:22:40
I want to take the data from an Excel file that I have uploaded and use that information to update an access table that resides in the same folder on a server.
How do I get started?
Do I need 2 connections open?
Can the sql statement do an Update where 2 fields match?
Dave
|
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Update access Table from an Excel table in ASP - 4/30/2008 18:31:14
Thanks for the response.
I am able to query the excel data just fine and the access data also.
Now I don't know how to take the excel data and update the access data.
I have never opened 2 tables much less 2 dbs at the same time.
I have updated from forms, but not tables.
Can you get me going?
Dave
| |
|
|
|
rdouglass
Posts: 9137
From: Biddeford, ME USA
Status: offline
|
RE: Update access Table from an Excel table in ASP - 4/30/2008 22:46:16
OK, are you familiar with arrays?
When you say UPDATE do you mean just UPDATE or UPDATE and INSERT where necessary?
If you know there's always a "1 to 1" relationship, you can read the Excel file into an array and then loop thru the array building your update statememnts.
It's kinda' like saying "get me started on building a car". It's hard to get you started without knowing what kind of vehile you want and what you have to work with.
OK, having said that; this is probably the basics you're looking for.
1. Let's assume for simplicity sake that you're key is an Access PK field called ID and the matching ID is in the first column of the spreadsheet.
2. Let's also assume that we have 1 text field to update. In the Access DB, it'll be called 'myText' and it will be the second column in the Excel file.
So we assume you already have your connection to the DB built but we're *not* going to use recordsets but rather 'getrows' which will put our Excel data into an array:
'First, read your Excel data into an array:
Set conntemp = Server.CreateObject("ADODB.Connection")
conntemp.Open "DRIVER=Microsoft Excel Driver (*.xls); DBQ=" & Server.MapPath("/fpdb/myFile.xls")
set rstemp=conntemp.execute("Select * From [$sheet1]")
IF Not rstemp.eof THEN
myArray = rstemp.getrows
End IF
rstemp.close
set rstemp=nothing
conntemp.close
set conntemp=nothing
'Now set the Access DB connection and loop thru the Excel date
'creating UPDATEs as we go
Set conntemp = Server.CreateObject("ADODB.Connection")
conntemp.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath("/fpdb/myDB.mdb")
FOR i = 0 TO ubound(myArray,2)
conntemp.execute("UPDATE myTable SET myText = '" & myArray(1,i) & "' WHERE ID = " & myArray(0,i))
NEXT
conntemp.close
set conntemp=nothing
Now I didn't do any error checking nor checked any syntax and that's strongly reccommended; it's right from memory so I'm sure there's probably at least 1 problem with it. But I think the concept should get through.
1. read the data into an array using getrows.
2. loop thru the array creating UPDATE statements
3. execute each update during the loop
Does that make sense at all? If not, try brushing up on arrays. We're using a 2 dimensional array here.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Update access Table from an Excel table in ASP - 5/1/2008 7:15:31
Interesting!
In the end there will be an Update one to one for the files that match, a Delete for the files that no longer exist and an Insert (Append) for the new records.
I have worked a bit with Arrays, they scare me :-(
Arrays are something I need to learn, but the learning curve has been difficult for me.
But I will take all the ideas I can get.
Thanks
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Update access Table from an Excel table in ASP - 5/1/2008 8:36:22
It's sort like an inventory. The basic items stay and particulars about them change from the main server, but the item number and quantities on hand stay in the local db (Access).
When the new data comes via excel, we update the particulars like the description and number that should be on hand, delete those items that have are to be removed from our active inventory and add the items that we will have to carry.
So the data is access which we use to manage the business remains as long as they are to be in the active inventory. We do not get rid of where they are stored, quanties we do have, who manages this inventory.
Confusing I know, hurts my head also.
I do apreciate your help. I am firing up now and see if I can apply the concept code you sent.
Dave
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Update access Table from an Excel table in ASP - 5/1/2008 9:37:23
OK I am getting there - this is good. I can see the data in the array- a good thing.
In my excel file I am using a defined area so can I use the Excel field name in the update
UPDATE myAccessTable SET myAccessName= '" & myExcelArray(1,i) & "' WHERE AccessID = " & myArray(0,i)
Ok how would apply Excel field names. I know I can see the Excel array data by using a field name
The reason is that if someone decides to do me a favor and add more Excel fields or change the order of the Excel fields the data will be messed up.
Thanks for staying with me. I feel I am getting close :-)
Dave
| |
|
|
|
rdouglass
Posts: 9137
From: Biddeford, ME USA
Status: offline
|
RE: Update access Table from an Excel table in ASP - 5/1/2008 9:43:22
quote:
Ok how would apply Excel field names.
Not sure what you mean there. Do you mean columns? You can always address columns in a 2-D array using the first 'ordinal' of the array:
myArray(columnNumber,rowNumber)
but remember they are '0-based' meaning the first column is 0 and not 1.
That what you're looking for?
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Update access Table from an Excel table in ASP - 5/1/2008 9:50:49
Sorry
I want to use the Excel Field (Column) Name in the array and match it to the appropriate Access Field (Column) Name
UPDATE myAccessTable SET myAccessFieldName= '" & myExcelArray(1,i) & "' WHERE AccessID = " & myArray(0,i)
would Equal
UPDATE myAccessTable SET myAccessLastName= '" & myExcelArray(1,i) & "' WHERE AccessID = " & myArray(0,i) so maybe it is this?
UPDATE myAccessTable SET myAccessLastName= '" & myExcelArray(ExcelLastName,i) & "' WHERE AccessID = " & myArray(ExcelID,i)
would the 1 in the myExcelArray exmaple be myExcelArray(ExcelLastName,i)
and the 0 would be AccessID = " & myArray(ExcelID,i)
| |
|
|
|
rdouglass
Posts: 9137
From: Biddeford, ME USA
Status: offline
|
RE: Update access Table from an Excel table in ASP - 5/1/2008 10:00:18
quote:
IMO you should remove any column header data from the worksheets and be sure the first 5 cells of the column contain the correct data type.
Also, remember what I said right there. If you have column names as the first row of the SS, they will be interpreted as data and *not* column names.
_____________________________
Don't take you're eye off your final destination.
ASP Checkbox Function Tutorial.
| |
|
|
|
DaveKstl
Posts: 547
Joined: 4/21/2004
Status: offline
|
RE: Update access Table from an Excel table in ASP - 5/1/2008 10:06:06
The column position might change if fields are added or if someone gets creative and switches the positions.
So I am trying to guard against that possibility.
I think I can pull off the Array position myArray(1,i) with what you demonstrated earlier.
In my old world, I would pull the Access file down, run queries saved in access, then push the file back up. It took less than 5 minutes.
The Excel file was linked in Access. Now I need to pass the maintenance of the data to someone else and stay on the Dev side of the house.
Thanks
| |
|
 New Messages |
 No New Messages |
 Hot Topic w/ New Messages |
 Hot Topic w/o New Messages |
 Locked w/ New Messages |
 Locked w/o New Messages |
|
Post New Thread
Reply to Message
Post New Poll
Submit Vote
Delete My Own Post
Delete My Own Thread
Rate Posts
|
|
|
Printable Version 
