Attached Access 2003 File Showing Proof Of Concept For Off Site Data Handling

This has gone on hold for me until I upgrade to 135 but it may be of use to someone else.



I was looking at using Access - since I have a small bit of knowledge with it - for the off site data handling rather than an Excel spread sheet.



A database allows far more to be done than a spread sheet.



It is the way to go really and is what cs themselves should produce to sit along side the product. I am sure there will be some open source close equivalent to access by now.



It uses sample data originally exported from cscart 134 and all the data fields that it exports. (135 has more data fields and functionality in this area.)



It is not neat or tidy - since it was only a proof of concept idea and can be improved on no end with its appearance and functionality.



Reports could be added.



Automated import and export routines could be added for the link with cs-cart.



The bit that you need to look at if you are interested is the form which is where data would be input and viewed.



You will get a warning when you open it - which is just the norm since Access 2003 upwards. Though it does not contain any executable code. Perhaps look at the number of posts I have made to help decide whether to trust me!!



To large to attach - I shall be back with a solution.



Okay it can be downloaded here.



[url]http://www.filejumbo.com/Download/EF89C80A5577FC8B/[/url]

Page



I downloaded this and it is really pretty cool. I have a ton of experience with Excel, but almost none with access so it would take some time to figure some of this out. I have used Excel with csv files, but for some reason I always have difficulty with them. My difficulties don’t make since, since I am really good with Excel, but oh well.



I couldn’t tell if this would work with 1.3.5, but if it was set up to than I would probably use it. This would be especially nice since my internet connection is so slow and it takes so much time to add products and such.



Anyways, cool stuff. If you design this for 1.3.5 please post it here and I might use it.



Brandon

Yeah it would work with 135 and any set of data like that.

It would just have the extra fields added.



As I say it was proof of principle and all a bit of a tatty quick knock off mess.



Many people use Excel for what is really a database situation.

Why? Because they know it and it is easy to get going on. There is a much larger learning process before you can get much out of Access.

There is overlap between Excel and Access.



To give you a mini understanding. A database splits off the storage of the data from the data entry and the data display. Plus includes things to allow the manipulation of the data. (CS - cart as a whole is an example of a very sophisticated database).



In our case here for just handling product items.



You can clearly see the benefits in terms of data entry and viewing in what I have done already but this could be extended to include things like drop down select box for fields with a limited range of options - to simplify and reduce errors at the data entry stage.



You can very easily filter and then work on subgroups of data. Say all the products from just one supplier or just one category. If you have a play with what you have already then you will see what I mean.



You can very easily changed the order in which the data is displayed to work with it by applying a sort order. Again you can have a play with what you already have.



The system could also be used to produce reports either on screen or in a printable format - on nearly anything you wanted and in nearly any way that you wanted.

For example all the products that from a certain supplier but just showing the product name and reference number and price. Or the same but now restricted to products with a selling price between £5 and £10 only. Or all the products that are currently out of stock etc etc.



Getting a bit harder and more “dangerous” but you can do things like run “queries” and have prices from say a certain supplier updated by 10%.



These type of things are pretty easy to set up and do in a quick and dirty way and knocks spots of working with Excel where the way is even dirtier.



You can look at some of this stuff just playing with what is already in there.



The bigger work comes if you are then extending the database so that it is designed to work for dummies and hold their hand every step off the way.



A couple of macros would ideally need to be added one to import the data exported from cs-cart and the other to export the data ready for cs-cart. Doing this manually is not hard to do - once set up - but why go through ten things when a single button press can do it in seconds every time with less chance of errors.



For myself one problem I have is that although 135 is far better than 134 in the import/export arena it is still a bit of a halfway house when you have products with options - in that it cannot export and import the option details so I would still be stuck with having to mix online and offline work on products. Damn damn damn.



If you are an expert on upgrading cs-cart I would be happy to develop a version for 135 and a more “proper” one in exchange for you sorting out the upgrading my cart. See it as a labour exchange! I think you would be a clear winner in hours but I would also get benefit from the Access thingy.

When I have tried I just got various errors and sadly don’t have enough money from the site coming in to throw at it nor the knowledge about sql and the nitty gritty of servers etc.



Once the structure was in there and with a few sample queries and reports you could learn to create your own custom reports etc.



Access is pretty easy to use it is just that you have to do a lot of learning before you can do much with it IF you are starting from scratch. Have a play with the one that you have downloaded and you will see what I mean. In the form view click the design button and see how easy it is to move stuff around and resize it etc.

Maybe try creating your own report. The access wizards are pretty good and when you have seen how one is structured then it is easier to work out what to do to get what you want.



Do you know that excel has a form view? Not very good but it may help you.



Maybe CS-cart will develop something!

[quote name=‘brandonvd’]Page



I downloaded this and it is really pretty cool. I have a ton of experience with Excel, but almost none with access so it would take some time to figure some of this out. I have used Excel with csv files, but for some reason I always have difficulty with them. My difficulties don’t make since, since I am really good with Excel, but oh well.



I couldn’t tell if this would work with 1.3.5, but if it was set up to than I would probably use it. This would be especially nice since my internet connection is so slow and it takes so much time to add products and such.



Anyways, cool stuff. If you design this for 1.3.5 please post it here and I might use it.



Brandon[/quote]



If you opened an Excel spreadsheet in notepad, you will get a jumbled mess. If you open a CSV file in notepad, you get a comma delimited list.



So in other words, the way those 2 files are read are completely different.

Matt



Not that you are wrong or anything, but what are you talking about? I get the notepad thing, but I have no clue where it came from. Could you explain a little bit more?



Brandon

I am puzzled also why this has come in.



You can import csv files - into and from excel - and into and from access. Where they can be worked on rather more easily that in a txt file!



If you download that sample Access file you will see what I mean.

really interesting idea. If the access database can be linked live to the mysql database, that would be great. I do that currently with my shopping cart (not CS-Cart). It runs off SQL Server on the server, and I am able to link to it via Access on my own computer.



if that is not possible, another option is to use a tool like CodeCharge. I use that to create forms for the web. That way you can keep your data in one place on the server.

in looking it up, you could potentially link access direct to the mysql database:



[url]Information Services Division | Information Services Division - UCL – University College London



[url]Information Services Division | Information Services Division - UCL – University College London

Wow not thought of that - but I won’t be doing it.



Beyond my skills and I also think there may be a lot more to it since cs-cart may do more to the data as it imports it. At a minimum it checks it.



Dabbling into their datastructures could cause big big problems.



The main idea was also to be able to work offsite and so quickly. Then with an upload.