Exporting Access data to Excel

[Guest]

I need to be able to export data from an Access Query into a certain format
in a spreadsheet. Does anyone know how to manipulate the data from the query
such that the data shows up in specific cells of excel?

My ultimate objective is to export data from Access to Quickbooks so if you
know how to create .iif files from Access query or table data that would
be even better.....

Thanks.

FJ

 

[Guest]

As a QuickBooks and Access user of long (and sometimes bitter!) experience,
if you succeed in writing the necessary to get directly from an Access query
to an iif file, you're a better man than I am, Gungadin! (I'm female, so
maybe that wouldn't be so difficult). You could either:

Look at Big Red Consulting's Iif Transaction Creator - you create a normal
sort of spreadsheet, then use it to generate an iif file,

or

Look at qodbc.com. They have a well-developed odbc driver for QuickBooks
with which you can (as I have) develop Access update queries which can
directly amend or add QB transactions. It may take quite a bit of work to
become familiar and comfortable with it, but once you have, the possibilities
are extensive and powerful.

 

[Guest]

Yes I've been reseraching this and I suspect you're right although the
prospect scares me as I'm a rank amateur at all this and my needs are very
simple, just a few general journal and check lines. I had hoped someone
might have an example VBA code I could adapt (with help from some friends).

In addition to the QODBC have you heard of QImportData by a company called
Accountigrate? I think this accomplishes the same task by it incorporates
some error checking as well. Not sure how much it costs though.....

Thanks for the tip.

FJ

 

[Guest]

I've taken your advice and downloaded QODBC. I'm not sure if I've done this
right but I've linked some of the QB tables into Access. However, I still
don't have a clear idea of how I get the data from my queries into the proper
QB fields.

1 line of The Query output is as follows:

DiscFees ConsFee Rebate Reserves DateClosed Company
-20 -10 -6947.2 6977.2 4/3/2006 XYZ Corp.


The .iif format for exporting this query line to QB would be:

!TRNS TRNSTYPE DATE ACCNT NAME AMOUNT
!SPL TRNSTYPE DATE ACCNT NAME AMOUNT
!ENDTRNS
TRNS GENERAL JOURNAL 4/3/2006 Reserves XYZ Corp. 6977.2
SPL GENERAL JOURNAL 4/3/2006 DiscFees XYZ Corp. -20
SPL GENERAL JOURNAL 4/3/2006 Rebate XYZ Corp. -6947.2
SPL GENERAL JOURNAL 4/3/2006 ConsFee XYZ Corp. -10
ENDTRNS


You can see that my needs are extremely simple. However, if you can give me
any guidance as to how I'd physically get the query records into the
appropriate QB fields I'd appreciate it. Also, I' don't want them linked
real-time. I want to transfer the data on a batch basis. I set the Optimizer
tab in QODBC to "The last time I pressed one of the data load buttons". Is
that correct?

Thanks in advance for any guidance you can give.

 

[Guest]

I have made the QODBC connection and can see the QB tables within Access.

I only have 3 simple transaction types that I need to export to QB weekly
(see below). However, after reading the documentation it’s still not clear
to me how to do this.

Any assistance you could provide would be greatly appreciated.



a) How do I actually send the output from my Access queries into the proper
QB fields? I know how to make an Update Query but since the QB tables nad
Access tables have no relationships through common key, how does the access
data get transferred to the right recors and fields?


b) Do I have to fill in ALL columns of a table when adding a record? For
example when adding a record for a check, (eg, #1 below) I have at most 7
fields to add but the CHECK table has numerous columns. Do I have to fill in
the others too? If so, how, with what data?


c) My base currency is Canadian Dollars but I have US transactions as well.
When I buy an asset from my US$ chequing account, the offsetting asset
account debit is recorded in Canadian Dollars. Do I need to export the FX
rates. If so, in which Table/field does it belong? For example, there are
exchange Rate columns in both the Check and CheckExpenseLine tables.
Also, when I look in the QB Checks table, none of the US$ checks I’ve written
appear. Where are they located? What table do I use for adding foreign
currency cheque data?


d) Are the transaction mappings below correct? (I’ve used abbreviated field
names)



Thanks in advance. Any assistance would be appreciated.
Jeff




1) CHECK transaction:

Check Table:

DATE, ACCNT, NAME, AMT, MEMO, REFNUM, FX RATE
4/16/07 Bank Acct BBB Co -80 memo 78 1.15

(does the FX rate default to 1 if the bank acct is domestic or does 1 need
to be imported too??)






CheckExpenseLine Table:

ACCNT, AMT, MEMO, CUST. NAME
Oth Curr. Liab A -5 memo BBB Co
Oth Curr Asset A 100 memo BBB Co
Income Acct -5 memo BBB Co



2) GENERAL JOURNAL transaction:


JournalEntry Table:

DATE FX RATE REFNUM
4/19/07 1.15 123 (is this assigned by QB if left
blank or not input?)



JournalEntryLine Table: (is this used for both debit and credit splits or
do they goto the separate DebitLine and CreditLine tables??)

ACCOUNT, AMT MEMO NAME

Other Curr.Liab. Acct A, 300 memo XYZ
Corp.

Income Acct A, 100 memo XYZ Corp.

Other Curr.Liab. Acct B, -400 memo XYZ
Corp.



3) DEPOSIT transaction:

Deposit Table:

DATE ACCNT MEMO FX RATE
4/16/07 Bank ACCT memo 1.15


DepositLine Table:

ENTITY NAME ACCNT MEMO CHECK# PYMNT METH. AMT
ABC Inc. Other Curr.Asset memo 9876 Wire
-90