Link to home
Start Free TrialLog in
Avatar of REIUSA
REIUSAFlag for United States of America

asked on

Need to write program or script that will update Access DB from website page or Excel spread sheet

I need to write a program or script or something that will allow me to update a database with as little effort as possible. Mainly I need help determining what language, products or tools I should look at that will help me to accomplish this goal. One thing to keep in mind is I am planning on building a retail website of my own at some point in the future so if it is possible to learn one thing and kill two birds with one stone then great, if not then the main thing at this point is to write the program to update my database. At the moment I am manually updating the DB with about 150-200 sales a month, not a lot but it is growing and I can see the need to automate as much as possible in order to grow in the future.

Maybe I am expecting too much and am way off base, biting off more than I can chew or there is a better and easier way than what I am trying to do but I am sure it can be automated, please let me know. Also maybe I need to use something other than Access for the DB to make it easier to do this, I’m open to any suggestions. If needed I could update everything I have now and cut over to a new DB system for future items and sales.

Some details:
The database is a Access 2007 DB. The business is a retail site in Japan that is similar to Amazon where different sellers can list their items under their account. I think there are two possible options. One is to communicate directly with the web page and update the database and the other is to use a excel spread sheet with sales listed and update the Access DB with that info. I update the Access DB with the inventory number and details before it even goes on the site so the data is there but needs to be updated when the items sells.

The main criteria I need is when the items sell is… the amount it sold for, fee’s, the inventory number of the item, and date sold. Maybe a few more but these are the main things.

Option 1: Update from website.
When a item is listed it can be access from a web link like www.site.com/ME/XYZ123 (XYZ123 is the item number) and the basic info I need is listed in different areas as text and numbers. One possible problem is text is in Japanese although I could probably translate the page.

What I would need to do is pull info from this page and input it into a corresponding record in the database based on the item number. Like take the date sold and input it into the DB for that specific item. And even maybe even a check mark field so I know it’s been updated.

Option 2: Export sales records to a Excel spread sheet and then pull from that data to update the DB. On one side it seems like using the Excel file is the easiest way to do it.
I can’t really import the Excel data into the Access DB since the records are already there for inventory. The row headers are in Japanese but it would be very easy to download a month of sales and manually update the headers.

I would need to pull xx data from item xyz123 from the excel file and input the data into the correct field in the DB for item number xyz123.


What I am aiming for is something I can simply run and it will go out and grab the data from the site or a pre-prepared excel file and update all of the records in the DB instead of manually checking the site or sheet and manually updating the info in the DB. This will make my life much easier going into the next year if I can work it out.

Thanks for any help and advice you can offer.
Avatar of koudry
koudry
Flag of United Kingdom of Great Britain and Northern Ireland image

Hello,

There are many web technologies that will easily work with Microsoft Access database like Microsoft Active Server Pages (ASP) - see http://msdn.microsoft.com/en-us/library/aa286483.aspx. For this your platform needs to be a Windows and must support Microsoft IIS server.

You can also use Java to connect to your Access database but Java is too heavy.

OK, now why do you want to use Access database when there are other more flexible options such as MySQL which is an Open Source database? I don't want to change your mind but think about the following combination:

- MySQL database - Open source - http://www.mysql.com/
- Apache web server - Open source - http://www.apache.org/
- PHP scripting language - Open source - http://www.php.net/

The above combination is the most widely used web technologies today. The reason for their popularity, is not only because they are open source but also because they are portable, i.e. run on Windows, Linux and UNIX.

From a business point of view, I am confident you can achieve all the business functionalities you listed above.  You can even develop a sales facility on your web site so that sales records can be entered on the system in Japan instead of having to import them from a spreadsheet.  However, if you want more control, yes, you can have a single sheet spreadsheet in CSV (Comma separated variable) format, as this is easier to import.  You just need a facility to upload the file on your web site and another facility to extract the content, all on your web site.

The requirements you have specified above, are quite common for web applications. But I don't want to go into any technical details at this stage.  

If you are intending to build the web site yourself, I would suggest you start looking at the above technologies. I am not expert in these technologies but I can try my best to advise.

Thanks.

Avatar of jason987
jason987

So the remote site will export the data to an excel sheet?

It's pretty easy to build a macro in a personal workbook that automatically dumps the data into an access database.  If you're looking to just get going I would go that route but if you want to learn and build a robust solution then go with koudrys^ advice.

Avatar of REIUSA

ASKER

koudry:
Is what you are referring to creating a website that will pull data from the retail site and update the DB connected to my web site instead of an individual Access DB on my PC? Is that better than writing something that I just keep on my PC and run to update the website? Although like I said if I can learn something that will help with another web project I have that would be even better. I guess I am just trying to get the most bang for my study buck so to speak.

One of the problems is I don't own the retail site (think listing items on Ebay) so I don't have access to the guts of it so much other than the item pages connected to my account and the excel file I can export.


jason987:
I was thinking using the excel file might be the quickest to get something setup sooner but I still need to pursue koudry's advice because I will definitely need something more advanced at some point but short term if I created a Macro do you have any suggestions on getting started? I have never created a macro, is there something specific I need to learn like VB?
Hello,

Thanks for providing more information on your requirement. If you want the application to sit your PC, then the scope of my proposal can be scaled down. I have the following options:

Option 1 (Access + ASP + IIS): You currently have an Access database. You can still keep your Access database but provide a web front-end using Microsoft Active Server Pages (ASP) that will let you import data from your excel into the access back-end database.  

The document @ http://en.wikipedia.org/wiki/Internet_Information_Services shows the list of Windows OS versions and flavours that support IIS. This means that you can a full-blown IIS version on your PC. For info, could you please tell us your Windows version  and edition etc?

If you stay with this option, you just need to write you ASP script to read data from your spreadsheet into your access database [see http://bytes.com/topic/asp-classic/answers/445647-read-data-excel-using-asp]

Option 2 (importing excel data into Access): you can also directly import your excel data into your access database using the import external data facility in access. You do not need to write any web application for this. [see http://office.microsoft.com/en-us/access-help/import-export-and-link-data-between-access-and-excel-HP001095095.aspx and http://stackoverflow.com/questions/74162/how-to-do-insert-into-a-table-records-extracted-from-another-table]

Option 3 (Apache + MySQL + PHP): as I suggested in my first post, you may want something more sophisticated and portable, i.e. that you can deploy anywhere (on your PC or web hosting platform).  

If this is just for your, then your PC is the best place for it. This means that you save money on web hosting and domain name. But no one else can see the application.  This is is a very good way to learn these technologies. The good news here, is that, you can run the application on your PC to start with and one day, if you want to go public, you can put it on a web hosting platform via the Internet.

If you want to use PHP and keep your MS Access db, this is an example of how you can connect to your access - http://www.actionscript.org/forums/showthread.php3?t=21969

Also how to ready excel data using PHP @ http://forums.digitalpoint.com/showthread.php?t=24351

I will leave the choice to you. If you require further info, we can dig a bit more.

good luck.


Avatar of REIUSA

ASKER

koudry:
Thank you very much for the info. I think short term just to get me up and running option 2 is best and once I get that going I would like to start learning Apache, PHP and MySQL since I am also planning on building a website hopefully before the end of the year.

In your opinion how long should I plan on taking to learn Apache, PHP and MySQL enough to get a decent site going? I have decent amount of IT experience but not much programming, some batch scripting but nothing hardcore, I generally pick up tech stuff pretty quick but again never dug into programming too much.

I'm going to start looking over the two links for options 2 and see what I can determine and report back. Seriously, this will be great to get up and running soon and then I can start working on the good stuff.

Hello,

I agree that option 2 is best for short term. I also agree on taking on Apache, MySQL and PHP. You should be able to write a decent PHP program in a matter of month or even less.  The starting point, is the environment, i.e. successful installation of the 3 components and get them to talk to each other.

For PHP programming, some people download a full WAMP package (http://www.wampserver.com/en/download.php). However, I have had problem with this. So I have installed the components one a the time, starting with MySQL, followed by Apache and lastly PHP.

Usually, PHP is problematic (i.e. you write the script and the Apache does not translate it) because Apache does not know about PHP until you tell it.  But I discovered that when I installed PHP version 5.3.4, it prompted me to choose which module I wanted to use for my web server and I chose Apache.  This means that it updated the Apache configuration file httpd.conf automatically with the following entry:

#BEGIN PHP INSTALLER EDITS - REMOVE ONLY ON UNINSTALL
PHPIniDir "C:/Program Files/PHP/"
LoadModule php5_module "C:/Program Files/PHP/php5apache2_2.dll"
#END PHP INSTALLER EDITS - REMOVE ONLY ON UNINSTALL

The php5apache2_2.dll file, is key to get PHP and Apache to talk. But I have noticed that some PHP installations do not have this file installed. This means that you have to download it and install it manually and then configure the Apache configuration file httpd.conf as above.

If Apache and PHP are properly configured, then your first PHP script will work wonder. So once you pass this hurdle, your next challenge is to get PHP to talk to MySQL. And once you have done that, the rest of the road is clear.

We will come back to this, once you are ready to make a start.

Good luck.
Avatar of REIUSA

ASKER

I'm still looking into this but I tried the first link and when I import the excel data it seems to over right what is in the access database instead of updating one of the fields.

On the second link for queries, I haven't used queries a lot but I thought they were for looking at specific data in a database. Do you do it a different way to import data from externally?

Should I open a new question for this specific task?

If you look at the two pics attached. Basically I need to take the line with the number "E31013" and find it in the Access database and then update the "Sold For Y" cell with the cell from Excel. All the other cells need to stay the same. Ignore the actual numbers in the pics this was just an example to try and show what I am trying to do.
 User generated image User generated image
Hello,

I will get back to you on this. We can stay on this question unless the moderator complains.

Thanks.
Avatar of REIUSA

ASKER

Thanks a lot!
Is this for internal use only?  MySQL is free for development but it is pretty expensive otherwise.

There is an option which not many people consider - SQLite.  It is built into PHP so all you need to do is enable it.  Not particularly fast if you have megaloads of records then it may be a problem.  No need to install or be admin/root to set passwords either - there are no passwords!
Hello,

@REIUSA

I had a look at few articles on the Internet to understand how we may be able to import data from Excel into Access. I have explored two scenarios including appending data from Excel to an existing Access table and also updating data in the existing table using data extracted from the Excel imported file.

I am not sure if you can make sense of what I have put together but if this does not work, I would ask you to post your database and Excel file with some dummy data. I can then write up the queries and we see what happens. I am using Access 2007.

@CUP

My understanding is that, MySQL, as Open Source product, comes in two flavours, i.e. a community flavour, i.e. free and a commercial flavour, i.e. not free. I could be mistaken but it seems like you can use the community version for education, learning and even commercial purposes. The only difference is that you do not get a formal support. Do I understand this very well? Please correct me.

Thanks.


EE-importExcelToAccess.zip
Yes, you're right - there is a community edition that I didn't know about.  This didn't exist when I last looked at the licences n years ago.  Wish it was available then: would have saved us restricting the use of one of our products.  Thanks for the update.
Hello,

Any progress on this?

Thanks.
Avatar of REIUSA

ASKER

Hi, sorry for the delay. I've been out most of the week but I am going to start looking at this now. Thanks for your help.
Avatar of REIUSA

ASKER

koudry:
This is helping a lot. I get the idea behind it a lot more now and I thin kit is getting closer to what I need. One thing I wanted to check on though, when I run the update query it doesn't seem to update the sales table with the number in the sales2 table. When I run it it shows totalsale with the number from the sales table. Am I running it wrong or not applying it correctly?

If I understand correctly when I run the update query it should take the number under totalsale form the sales2 table and then update the sales table with that number for the productcode of HHD. Does that sound right?

Thanks,
Avatar of REIUSA

ASKER

Here is a test copy of the database. Table Inventory is the master table and I imported a excel file similar to what you did as Sales_Record so this would be the info I can get from the site. I need to use the Inventory number "E 12345" to locate each item and update the "Sold Date" and "Sold For Y" data from the Sales _Record table into the Inventory table.

Does that make sense?
Database.zip
Hello,

You are right about using totalsale from sales2 to update totalsale in sales for product code HHD.  That is in theory what I was trying to achieve. Now I need to check to be sure it is working. I will confirm that later.

I will also take a look at your database. I will post back later.

Thanks.
Hello,

I can confirm that the update query on my db, is working and I have written another update query on your database which appears to be working too. Below is the update query:


UPDATE inventory
SET Inventory.[Sold Date] = DLookup("[Date Sold]","Sales_Record","[Inv Number] = 'E 12345'"),
Inventory.[Sold For Y] = DLookup("[Sold For Y]","Sales_Record","[Inv Number] = 'E 12345'")
WHERE inventory.[Inv Number]='E  12345';

When I first ran this query, it did not work. Then I realised that inventory number on the Inventory table has two spaces after the E. So I have added the two spaces and now it works.

I am sending you the database again with the update query. Please let me know how you get on.

Thanks.

 


 Database.zip
Avatar of REIUSA

ASKER

When I run the query, salesRec-select-query,  it returns the "Date Sold" and "Sold For Y" info but I'm not sure how to make that update the record in the "Inventory" (Master) table. Is there another step I'm missing in order to get it to actually update the Inventory table?

I'm sorry, please bear with me, I am not very familiar with running queries in a DB.
Avatar of REIUSA

ASKER

When I try to run the "update-query" it doesn't seem to do anything. I looked at it in SQL view and clicked the run button at the top and nothing happens.

Thanks,
Hello,

I used the salesRec-select-query to check that there is a match on the inventory number relating to the data to be used from the sales record table to update the master table. Once this query returns some data, it means that there will be data to be retrieved by the DLookup() function on the update query.  If this select query returns no data, it means that there will be no data to be used to update the master table.

In the update query, the select query above is replaced by the DLookup().  I have noticed that it is not possible to use the nested select query in the update query although other database systems will let you do that, so I have to use the DLookup() function instead.

I have tested the update query and it does work. But I have noticed a caveat.  The inventory number on the master table, has 2 spaces after the E instead of 1 space.  So when you do the match using the inventory number value, you need to make sure that the space is consistent.  

I suggest for testing purpose, you should try using an inventory number (in both sales record and master tables) without space to see if the update query works.  Sometimes it is difficult to have the exact number of spaces unless we use another function that can trim the spaces off.  I will try this if it comes to that.

In the database I returned, the master table was already updated. So you may want to check and change with some new values.

Have a go and let me know.

Good luck
Hello,

Any luck?

Thanks.
Avatar of REIUSA

ASKER

First off I apologize I am replying so late again, I know it seems rude and I am sorry. My circumstances have been very difficult lately but i will keep a closer eye on this thread next week.

Thank you very much, I tried it again and realized that I had disabled the security bar in Access before so once I enabled that and marked this DB as safe I was able to run the salesRec-select-query and then run the update-query and it updated the Inventory table!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I agree to I am going to get rid of the spaces or maybe use a _ instead, I was copying and pasting the number portion for something else I was working on and recently added the spaces so I could double click and just get the number but I am going to change it back.

If I modify the code for the queries so that it will pull from all Inv Numbers instead of just the specified E 12345 can I replace the number with a wildcard like * or % and enable it to pull from a big list of Inv numbers instead of one hard coded number?

The initial update is going to be about 700 items and once that is done about 150 - 200 a month.


Avatar of REIUSA

ASKER

I started playing around with it and looked some examples up online. I added one more record to the Sales_Record table and modified the salesRec-select-query with the below code, the main difference is I used LIKE instead of = and the * wildcard. It returned data for both records.

SELECT Sales_Record.[Date Sold], Sales_Record.[Sold For Y]
FROM Sales_Record
WHERE (((Sales_Record.[Inv Number]) LIKE "*"));

I used similar code for the update-query but I am doing something wrong...

UPDATE inventory SET Inventory.[Sold Date] = DLookup("[Date Sold]","Sales_Record","[Inv Number] LIKE '*'"), Inventory.[Sold For Y] = DLookup("[Sold For Y]","Sales_Record","[Inv Number] LIKE '*'")
WHERE inventory.[Inv Number] Like '*';


It seems to work fine for the salesRec-select-query since it returns different info for each record but when I run the update-query it updates the Inventory table with the same data for both records instead of the correct data for each individual record.

Here is an attachment with the updated queries that I am trying to use.

Thanks,
Database2.zip
Hello,

The sales record select query returns multiple records.  Since a single sales record is being updated, the update query will not work. I think there has to be a one to one matching, i.e. multiple data from sales record cannot be used to update a single sales record.

I suspect that some form of loop will be required to update one require at the time.  As a good number of required will be selected from Sales record, it may be a good idea to use an append query.

There are some example of update queries here [http://www.karlrixon.co.uk/articles/sql/update-multiple-rows-with-different-values-and-a-single-sql-query/] but the queries with loop are the ones for php.

Thanks.
Avatar of REIUSA

ASKER

Great, thanks. I'll start looking a tthe sites and see what I can find.

Thanks much for all your help.
Hello,

I had a think about this. I think the best way is to have a loop. In the first loop, you select all sales records as you wanted to do, i.e. "select * from sales record where condition". This could return one or more record. You then go through the loop and for each record in the loop, you check matching equivalent on the sale master table and carry out the update if a match is found:

------------------------------------------------------
select * from sale record where condition

start looping through returned record
   check master sales record for a match
   if a match is found then
      carry out update of master sales table
   end if
   move to next record
end loop
------------------------------------------------------

Now, the question is, can all the above be done in an access query?  I don't know but I will investigate when I have bit of time.  I know this can be done using a stored procedure on Oracle, MySQL etc but I am not sure about Access.

Thanks.
Hello,

As you can see from the URL below, some people think you cannot use stored procedure in Microsoft Access.  But at the bottom of the page, somebody threw that theory away. I will take a look later.

HOW TO: create stored procedure in Ms access @ http://p2p.wrox.com/access/33886-how-create-stored-procedure-ms-access.html

HOW TO: Create a Parameterized Jet Stored Procedure using DDL in Access 2000 @ http://support.microsoft.com/?kbid=202116

It is getting really complicated!

Thanks.
Hello,

I spent a bit of time trying to get some of the stored procedures to work but no luck. You may want to consider using a web front-end at this stage or a VB script or some form of procedural language to provide facility to loop through records.

I think we are trying to push MS Access a little too much. If you want to start thinking about the web front-end, please let me know. I have not given up on the Access though!

Thanks.
Avatar of REIUSA

ASKER

Thanks for all your help. A web interface is probably the best long term option so I am all for learning about that.

I'm going to post a quick question in the Query syntax forum as a last ditch effort to see if there is a way to get this going quickly while I work on better options.
ASKER CERTIFIED SOLUTION
Avatar of koudry
koudry
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Dirk Haest
I've requested that this question be deleted for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Avatar of REIUSA

ASKER

I apologize for the delay in coming back to this question. Please close out the question giving all points to koudry for helping so much. If possible please add the question to my knowledge base so I can reference it in the future.