Trying to figure out how to do this... Basically we have these templates for a daily report of defect parts/line/shift/date/etc. Right now the data is just saved in an excel file in a certain folder based on what line and what program is being ran that day. I've written a macro to basically save in the filename the date/line/shift when you click a Save button... Now, I want to have it submit the data to a database of some kind. I have the db setup and am fairly decent at SQL queries and all but I can't figure out how to get connected to the database. The few examples I've found online seem to be making it far more complicated than I expected it to be. I've also done some .asp work before in VB that involved a lot database transactions and I never had a problem with any of that...

Anyways, any basic tutorials you guys know about that dumb this down to the simplest level? I have to be able to INSERT into, not just use SELECT. I've found a few tutorials that focus on commands that are just for the select but don't work for insert...

Thanks,

2/19/2008 8:39:22 AM

are you still trying to/planning on keeping the data in both Excel and Access in parallel?
If you want to store the data in Access, why don't you use an Access Form to insert the data as well and leave Excel completely out of it?

2/19/2008 9:22:46 AM

I've considered that but am trying to make it so the operators (not the brightest) do nothing new at all. They already click a save button at the end of their shift and we have it setup to save in the right spots for them so they can't mess it up. Basically I want to add to that macro the code to insert this data into a database. The problem now is they just save then print these things off, they're saved and the data is never really in a position to be used..which defeats the entire purpose of having these in the first place.

So, yes I'd like keep the form in Excel. The access or SQL database side of things will be for me to use when coming up with statistics for whatever I end up needing

2/19/2008 10:07:58 AM

the best answer to this is probably a VBA macro

you can use DBOs in VBA, right?

2/19/2008 11:16:35 AM

Yeah ... OR for just a few dollars, you can get an Indian to write a simple web form that submits to your online database ...

Teaching them how to use a simple form shouldnt be that hard, unless there are specific reasons why you NEED to make them keep using excel that we dont know about.

Really, the web db/form would be super simple

2/19/2008 11:48:51 AM

Quote :

"the best answer to this is probably a VBA macro"

no shit.

2/19/2008 11:51:37 AM

so is something like this more complicated than you were expecting it to be?
http://www.exceltip.com/st/Export_data_from_Excel_to_Access_(DAO)_using_VBA_in_Microsoft_Excel/426.html

2/19/2008 12:02:50 PM

Doing this in SAS is trivial. It would look something like this:

proc import
  datafile="myspreadsheet.xls"
  sheet="mysheet"
  out=work.mytable;
run;
libname mydblib odbc dsn=xxxxx uid=xxxxxxxx pwd=xxxxxxxx;
proc copy in=work out=mydblib;
  select mytable;
run;

2/19/2008 12:08:05 PM

^^ no, that looks great and exactly what I was looking for. Some other examples I found had all kinds of session shit in them that didnt seem needed at all... Anyways, I'll work from that and see how it goes.

Thanks!

2/19/2008 1:04:45 PM

^^ that's great to know, and so relevant to what he's asking.
In other news, he could implement this in Matlab too!

2/19/2008 1:13:14 PM

Quote :

"Doing this in SAS is trivial. It would look something like this:"

did you not read the thread?

although SAS is an excellent, wonderful, and innovative system (just in case my boss reads this), it is not appropriate in this situation.

2/19/2008 1:25:26 PM

yeah, he knows that, but he just had to throw in his $0.02 about how his solution would be "better" and show that he knows SAS and we (at least most of us) don't.
kind of like how anytime someone asks a programming question about Java or C or PHP, the Perl fanatics just have to jump in and show how much easier the solution would be in Perl

2/19/2008 2:07:16 PM

My intent was to show that it could be done in SAS with a minimal amount of effort and code. I overgeneralized, however. I interpreted the problem to be "I have data in Excel, and I need to put it into Access".

I took the line "that dumb this down to the simplest level" too literally. My mistake.

2/19/2008 2:40:25 PM

Quote :

"Java or C or PHP, the Perl fanatics just have to jump in and show how much easier the solution would be in Perl"

honestly, sometimes, that is appropriate

perl is good for some things... most notably data processing
php is good for web applications
c is good for desktop applications
java is good for... well... nothing except cross-platform stuff

2/19/2008 3:22:14 PM

my overall point being: when somebody needs to use a certain tool (Excel, Java, C++, SAS, JMP, Matlab, whatever) or a set of tools (Excel + Access), it's often for a good reason, or sometimes for bad reasons that can't be changed (e.g. company constraints). Providing answers using completely different tools, while they may be technologically superior or more efficient or whatever, is often irrelevant. The job he's trying to do can probably be accomplished a 100 different ways better than using Excel and/or Access. SAS may be the absolute best solution for this, or an Oracle database, or maybe a Java backend and a web-interface. It doesn't matter, though, because those aren't the constraints he's working under. To use SAS, his company would have to buy SAS, install a license server, buy client licenses, train/hire/outsource someone to write the code, then train the assembly line monkeys to use the new interface. In the end, this might be the perfect solution, but it sure is a lot of effort for something that could otherwise be accomplished with 30 minutes of VBA.

2/19/2008 4:37:35 PM

Yeah, exactly.

They're going to keep using the excel forms--I just need to get more usable data out of them and this should make that very feasible with no changes visible to the end user. It's only a few fields that maybe constitutes 4 transactions a day.. Anything beyond writing 30 mins of VBA is complete and utter overkill.

Anyways, thanks for the help..didn't have time to work on it today but I'm going to try to make time tomorrow.

2/19/2008 6:37:06 PM

Quick update... that looks like it's going to work great. One problem I had before was not having the DAO reference turned on

2/20/2008 8:32:56 AM