Novice Question

[BobS]

Gotta start someplace - so here I are......

I maintain a friends business system for him. He owns an automotive service
/ repair shop and they use Mitchell1 software (amongst others) which outputs
the database to a .mdb format.

His accountant (wife) is just learning Quicken and you can see where this is
headed.....

Everyday she takes the paper invoices from the day before and manually has
to type in the data to QB - a pain. I know zip about either of the programs
really but I know that this should be able to be automated in some fashion.
Not having a good basis in accounting or programming, I started to look at
the built-in tools in Mitchell1, Access and QB. Mitchell1 does have a
program to convert to QB format (iff) but the rep says it doesn't work and
does not recommend it - he's also a friend.

Found that if I make up a Query in Access 2003 with the info needed (name,
addr, auto, inv, no., amounts, etc) that I can then have it exported in a
..xls, .txt or several other formats that QB will import. Perhaps other
methods in Access I could use but that is what I've learned so far. Okay,
so it looks doable and will need a lot of refining to insure the date range
is right, and so forth. Certainly nothing that hasn't been done before.
It's just invoice data, not a lot of it and pretty straight forward.

A program or script extract the data from the mdb file
A method to auto enter that data into QB

Simple..... So I went searching for such a program or a script that I could
use as a model. Found that these plug-in's are as costly as the program QB
itself ($399USD). Not saying it may not be worth that because I'm sure
their software has more functionality than what I'm looking for.

So based on the above, anyone have any recommendations to help solve this
little problem? Reasonably priced would be nice.... or a script I could
play with?

Thanks,

Bob S.

 

[Larry Daugherty]

Your problem space is QuickBooks, not Access. You need to *know* what
QB needs. You might google on the various terms in hopes of finding a
solution.

HTH

 

[Albert D. Kallal]

Mitchell1 does have a program to convert to QB format (iff) but the rep
says it doesn't work and does not recommend it - he's also a friend.

So you mean a company that likely spend hundreds of thousand dollars,
perhaps even millions to develop tihs applcation does not have a solution
for this? And you seem to think it's a trivial?

To be fair, it might not be difficult, but don't assume it is trivial. If it
was just a simple delimited export of a file that Quickbooks could use, then
why would the original manufacture not provide this feature for you?

Found that if I make up a Query in Access 2003 with the info needed (name,
addr, auto, inv, no., amounts, etc) that I can then have it exported in a
.xls, .txt or several other formats that QB will import.

The above sounds like the right approach. The difficult problem here lies in
that often an invoice is simply not a single record, but is a hodgepodge of
relational database information. So, how easy this task is to approach will
much depend on how the import system of Quickbooks functions.

Perhaps other methods in Access I could use but that is what I've learned
so far.

I do think you're on the right approach. if Quickbooks supports the invoice
information being imported by a simple csv text file, then that's certainly
the right approach.

A program or script extract the data from the mdb file

You may not need code at all. You simply build a query that has just exactly
the right fields you need. You then export this query as a csv file. If you
can formulate a query that has just the fields and columns of data that
Quickbooks needs, then this should should not be a lot of work at all. (this
is a big *if* however). If the csv file required by QB is such a format that
simple query export cannot produce in ms-access, then you're going to have
to resort writing code to produce that file.

A method to auto enter that data into QB

Well, I assume at this point it's going to be that inport facility that QB
has...

so, the process would look something like the following:

you take your MDB file, and export the data via our query into a csv file.

Fire up Quickbooks, and import this csv file.

So based on the above, anyone have any recommendations to help solve this
little problem? Reasonably priced would be nice.... or a script I could
play with?

As mentioned, you may not actually need a script that all. You simply need
build a query in the query builder with all of the fields, and then you
export that data as csv. We don't need any code to do this, you just
highlight the query, and then go file ->export to make a csv file.

So, at this point, what you'll actually need is the correct format of the
csv file that works for QB. Without that information, you're actually in the
dark here. So, #1, you need the format of that csv file required by QB (you
have to ask the QB people for that).

Then, #2, is look at the mdb file, and see if you can easily build a query
that has ONLY the collums required.

If you can do both of the above, then you are home free....

 

[BobS]

snipe.........,

Albert,

I'll first say thank you for your time in responding to my question but
there were a couple of points I think you didn't understand.

With you being an MVP in Access, then you should know better than I the
capabilities of the Query Wizard. I was able to export a "test bed" of data
to several formats (txt, csv) that I could read with a text editor and Excel
so I could insure the data from the mdb database was being extracted
properly. It was and I had the data I requested in those formats.

I have not been able to test the import function on QB 2007 to see if it
ends up in the right fields in the proper records. That's where I was hoping
someone may know what is required by QB 2007. I'm certainly not an
up-to-date programmer but I used to make a decent living writing hardware
drivers in assembly and then moved on to Pascal, Business Basic, Cobol a
little Ada and C - long ago. I have VB6, VBA, and a couple of scripting
tools laying around that have gathered a lot of dust and rust but if needed,
I'll fire them up again and see how much I've forgotten....

I did not intend to imply the plug-ins or the Mitchell1 effort was a trivial
effort but what we need is not exactly rocket science here. My bit of
testing proved that. I would have thought this would be an everyday task for
a database programmer and that someone could point me to something that was
reasonably priced or a bit of code to use as a model. To automate the task
as I said I wanted to, will take some programming / scripting.

1. Schedule script to run at COB to extract invoice data fields from mdb
file.
2. Perform simple validation of data fields, (insure data is for that day
and that there are no missing invoice numbers and there is alpha/numerical
data in each field), generate error messages if needed.
3. Convert data to csv, txt or other format that QB can read and store file.
4. Manually import the file into QB the following day and visually verify
each invoice.
5. After validation, email "Accountants Copy" of QB file to business
accountant.

Thanks,

Bob S.

 

[Albert D. Kallal]

With you being an MVP in Access, then you should know better than I the
capabilities of the Query Wizard. I was able to export a "test bed" of
data to several formats (txt, csv) that I could read with a text editor
and Excel so I could insure the data from the mdb database was being
extracted properly. It was and I had the data I requested in those
formats.

It wasn't clear to me at all that you already obtained and built a query
exporting the data that Quickbooks will accept. It's not a question of my
ability (or lack of) of what the query builder can do here. The problem we
**still** have to solve here is that we don't know if the mdb file produced
by that system will allow us to build a query to export the data in the
correct format for quickbooks.

Using the query builder OFTEN breaks down when tring to represent repeating
data (or more importantly suppressing repeating data). There might be the
requiement for Quickbooks input to recive data in such a way that a query in
ms-access CAN NOT produce the format requried. My point here without seeing
the data produced in this application, don't make the simple assumption that
a *correct* csv exported file can be created from a query. We *might* have
to write code to produce this data file, and NOT use a query here. If that
is the case, then your code will get messy. There is a text export sample
here that you *hopfully* don't have to use:

http://www.mvps.org/access/modules/mdl0058.htm

So, sure there is some code hanging around that we might use to export data,
but for the most part we don't need the above code.

My suggestion still stands that you'll need the format of the Quickbooks
import file, and then you'll have to look and see if you can create that
file in the correct format from query export from access. You may have to
massage the mdb data with some code BEFORE you make the export via a query,
but let's cross that bridge when we get there.

I have not been able to test the import function on QB 2007 to see if it
ends up in the right fields in the proper records. That's where I was
hoping someone may know what is required by QB 2007. I'm certainly not an
up-to-date programmer but I used to make a decent living writing hardware
drivers in assembly and then moved on to Pascal, Business Basic, Cobol a
little Ada and C - long ago. I have VB6, VBA, and a couple of scripting
tools laying around that have gathered a lot of dust and rust but if
needed, I'll fire them up again and see how much I've forgotten....

Right, and my number one suggestion was to get the format you need. I just
did a google on this newsgroup, and nothing came up on this side, you will
have to try the quickbooks side/people.
..

I did not intend to imply the plug-ins or the Mitchell1 effort was a
trivial effort but what we need is not exactly rocket science here.

Well, actually it might be trivial. This is just a minor cake crumb issue
one way or the other. I did not say one way or the other, I said simply
lets not make the assumption that it'll be easy. I much believe this export
should be quite easy, but one little small problem can trip up the whole
process, the next thing you know we are writing tons of code that we really
don't wanna have to write!

My bit of testing proved that. I would have thought this would be an
everyday task for a database programmer and that someone could point me to
something that was reasonably priced or a bit of code to use as a model.

Like I said, you might not need anything at all. In fact, I am 100% on the
same channel that this might be easy. I suppose it's possible that I just
do so much coding all the time, that any of the coding issues are absolutely
trivial to me, and the number one thing that will stop me from achieving
this goal here is the definition of the required csv file....

You simply build a query, and exported as a csv file. That is the suggestion
that a everyday database developer would give. You don't need anything more
in this regards at this point. As I said, if you can build a query that
produces the correct data format that you need, then you're home free. The
amount of scripting and code to export this data is going to be VERY small.
So, once again, this is why I'm concentrating on that we need the format of
that data you need, and number two, we have to determine if we can output
this via query + export spec.

Everyday access developers don't have any code "hanging around", because the
features and abilities to do this is built into MS access *if* we can use
the query builder.

1. Schedule script to run at COB to extract invoice data fields from mdb
file.

I have no idea what "cob" means, but if you want to execute and run
ms-access code via the windows task scheduler at a give time daily, then I
have an aritcle, and a sample script here:

how to run ms-access as a batch job
http://www.members.shaw.ca/AlbertKallal/BatchJobs/Index.html

2. Perform simple validation of data fields, (insure data is for that day

Ensuring date is for the particular day would not require code, it would
simply be part of the SQL criteria of the query we use to export.

and that there are no missing invoice numbers

Do you mean simply the field *must* have an invoice number? Once again no
code needed, and that would be part the criteria of the SQL (again, no code
needed). However if you're talking about missing invoice numbers in some
type of sequence wise, then that's a little bit more tricky code and write.

and there is alpha/numerical data in each field)

If the files been produced correctly by the other application, then the
field will actually be particular data type. So for example if you're
talking money amounts, or currency amounts, then the data field produced
should be of data currency type, and you will NOT have to test, or even
check if alpha data is in this collum, as it will not be possbile in the
first place.

Again, this all goes back to me saying to look at what the data produced by
that application looks like. If that other application is setting the
correct data types for the columns, then for currency and most fields you
will not need any validation at all. The setting of the data type for that
collum will solve this problem for you.

, generate error messages if needed.

3. Convert data to csv, txt or other format that QB can read and store
file.

The way we export data here is we FIRST manually do the export in access
(assuming we've built our query), and perhaps during this export set up what
we call a "export spec". You need to learn this feature of ms-access. You
use the advanced button during the wizard export. As you build the export
with the wizard, you then "save" the export "spec" name. Once you get the
export working manually, then you in code can specify this export spec:

The code looks like:

docmd.TransferText acExportDelim,"spec name","our query",strFileName

like I said, there's really not a bunch of code that we have to write here,
we just have to determine if the suppled data is in a format that can be
easily exported. You'll likely be surprised at the lack of amount of code
that you have to write to accomplish most tasks in MS access. Get the
sequence of events working manually by using the features of access, and
once you get going, then we can start slinging together some code that will
automate those manual steps for you.

 

[BobS]

Snipe.....,

Albert,

Wow... and thanks for enlightening me. You have given me enough rope that I
can now probably successfully hang myself on this one...;-)

I have Access here in my office and a copy of the database to play with as I
explained but not QB. Since this may take some trial and error, I'll have
to get QB on my machine so I'm not living at his shop trying things.

You made some excellent points, I'll keep those in mind as I go along and if
I get totally confused look for a post with "Albert - - Help!" in the
subject line.....

The term COB means - "Close of Business".

The format that QB can import is xls, xlsx, csv, qbo (web connect) and Quick
Books iff format. It looks as though my best option is to use csv since
that appears to be the preference for importing when iff is not used.

I do appreciate the help you've provided and your generous offer of
additional help.

Thanks,

Bob S.