I Need to Quitbooks Desktop, Database Planning - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

I Need to Quitbooks Desktop, Database Planning

Aleece Landis

5 months ago

I Need to Find a Replacement for QuitBooks Desktop since they are forcing people to switch to Online or Enterprise. And the current subscription price has gone up exponentially every year since they forced everyone into a subscription instead of letting us buy the software.
I am certain that the use I make of the desktop software could be managed with excel though I think it would be more easily done in a relational database.
I have been frustrated with the desktop software for years as I've had to do a fair bit of manipulation of the files to import them into QB and have had to subscribe to additional 3rd party software to help me do it in addition to having to either manually edit files for import or create programs to do the editing for me.
I am not a programmer, however I have managed to learn a few things in javascript, python, and VBA over the last several years.
I am confident in my ability to figure out how to implement a financial database (eventually) but I am more interested in a discussion on WHY certain things/methods/etc might be more appropriate than others.
Please help me find the appropriate place to read/research this.

	John Davy @Reply 5 months ago
Hi Aleece, Richard has done several lessons and has code that will do a great job. I have personally used his code and created a replacement for 2 Machine Tool Companies that were using QuickBooks (which was overkill for each). Access is still doing the job with ease. Take a look at Invoicing https://www.youtube.com/watch?v=NOZ9z-9a46w. Also the Extended Cut for this video. HTH John

Aleece Landis OP @Reply

5 months ago

John I've been working through Richard's Lessons, but that has more to do with the nuts and bolts of Access which I already have a fairly good grasp on the concepts and I'm mainly going through the lessons to learn details on Access.
I am checking out the invoicing and check register and double entry videos too (haven't gotten through them yet.)

What I'm really looking for is a good place to discuss/learn about the specifics for developing a replacement for Quickbooks. Like what are some pitfalls I should watch out for when it comes specifically to business/financial database design.

I haven't done much in the way of database work since University (at which time I was using Microsoft works database for doing the lighting plot paperwork.)

I feel far more adept at the computer stuff than I feel comfortable handling the financial bookkeeping. But, I get to do both.

	Garry Smith @Reply 5 months ago
I have developed a fair amount of bookkeeping DB's in Access for my businesses and other non-profits. Like yourself, I found QB to be too limiting, and they became very greedy. If you like, I can help you get started if you want to contact me.

	Tom Juric @Reply 5 months ago
I have always used access to fix my small business...NOT QuickBooks. I would imagine that the hardest thing is to find a service that will allow you to process credit/debit cards and checks. I did something similar for two of my customers using PayPal for the processing center. It is inexpensive and performs the same processing service as QuickBooks. Might be worth your checking out?

	Alex Hedley @Reply 5 months ago
Credit Card Template

	Aleece Landis OP @Reply 5 months ago
Garry Yea I might want to chat (is there a direct message feature on this forum?)

Aleece Landis OP @Reply

5 months ago

Tom I have never used QB to do any of my processing or payroll. I wish I had realized the change to QB was happening a few months earlier (I would have purchased the latest copy available for purchase and then just used that as long as I could without subscribing.) Unfortunately, I had an earlier copy and had to upgrade and suddenly found out the subscription was my only option (I will not go to ONLINE bookkeeping service, I tried that years ago and I'm in FL in an area where trying to get online via cell data is nearly impossible so when the cable and power go out, no internet. We have generators so if my software in on my machine, I can still print invoices.)
I am really just looking for the bookkeeping, account tracking, invoicing, and reporting.
I need to be able to import transactions from banks as well as handle my paypal, amazon, ebay and in person sales receipts and invoices.

Aleece Landis OP @Reply

5 months ago

FYI, It was not a typo in the Post Subject when I wrote QuitBooks. That is essentially what I meant to call it (I need to Quit Quickbooks.)

Anyway, I'm hoping for a little reassurance that my plan is appropriate or if anyone has recommendations on how to proceed Please say so.

Plan:
Export Lists and data from Quickbooks as excel files. (I have Zed Axis to help me with this step) these are the ones in my plan.
Chart of Accounts
Check
Class
Credit Card Charge
Credit Card Credit
Customer
Deposit
Employee
Invoice
Item NonInventory
Item Service
Journal Entry
Other Name
Payroll Item Wage
Receive Payment
Sales Receipt
Transfer
Vehicle List
Vehicle Mileage
Vendor
Vendor Credit

Then I need to import these into tables.
ChartOfAccounts
Class
Customers
Employees
Vendors
Items
Vehicles
Mileage
Transactions
(and probably a few more, I'm not sure if sales recipts and invoices need their own separate tables of if they belong in transactions and stuff like that is what I'm still figuring out.)

Tips?

John Davy @Reply

5 months ago

Hi Aleece, If you are thinking of creating a replacement for QuickBooks - I say Why? They have spent many more hours than you ever will- Just buy it. However; if you find features that are not used or not needed or if you need to add features that they don't have, then you can create your own in Access. Richard has written several apps which can be the basis for your application - Invoicing, Barcodeing, Inventory Management, Transactions, Credit Card, etc. Quite often field names may be different between components and you will have to make some adjustment to pull the pieces together, but it will be yours and no annual fees. I did this for 2 Machine Tool Companies and simply streamlined their bookkeeping to fit their needs and cut their costs and their work. Good Luck John

Aleece Landis OP @Reply

5 months ago

John I need something to replace the parts of QB that I use. I've been frustrated with QB for years since it really doesn't make life easier for me the way it works. I have to spend lots of time manipulating the the files in order to import them (like from the Amazon Flat files or my ebay reports or the Paypal reports or the payroll files.) And the fact that I can't just "buy" quickbooks desktop anymore, they are forcing people to go ONLINE. Currently I still have the subscription for QB Desktop but it is now over $1000 per year and they are trying to force everyone who wants to keep Desktop to upgrade to enterprise or switch to ONLINE (neither are viable options).

So, at this point, I need to get myself moved to Access. Yes I want to streamline this for myself. I know it is all possible, I've managed just enough in the programming department to know it is all possible. I want to get this done by April.

Joe Holland @Reply

5 months ago

I have been using QuickBooks for 33 years since the DOS version. The company has gone off the rails with their pricing. You certainly can build your own accounting database but it will take a lot of time and keeping up with payroll changes will be daunting. The alternative I found to QuickBooks is AccountEdge. You can read about it here: https://www.accountedge.com/quickbooks-alternative/ (I do not work for them nor get paid for referrals. It is simply the best desktop alternative I have find for a much lower price.)

Aleece Landis OP @Reply

5 months ago

Joe Thanks Joe, I will check it out. As to the Payroll, I'm not actually doing my own payroll, I'm using a service for it and simply downloading their files to import into my database for tracking/record keeping purposes so I'm not using Quickbooks Payroll functions at all. I've simply been using Quickbooks as a database for tracking and record keeping and it is clunky and time consuming to do what I need to do with it requiring me to create my own external programs to manipulate the data before I can import it. Seems to me I would be better off putting the time/effort into creating my own Database that will let me design the import programs right into the workflow for each import I regularly do. I realize it will likely take me quite a while to build this. Main help I probably need is from a pro book keeper to make sure I follow good practices for the financial record keeping stuff.

Abraham Breuer @Reply

5 months ago

I use Access as my main system and QuickBooks Online only as the place the final accounting ends up.

Access pushes data into QuickBooks using the official API.

I authenticate to QuickBooks, pull customers, accounts, and tax codes into Access, and push customers, items, classes, and invoices back to QuickBooks Online — all via VBA using the official API.

If a mistake is made (price, class, tax code, etc.), I can delete single invoices or bulk-delete invoices directly from Access and repost them correctly.

Setup is simple. You need:

Microsoft Access (Office 365 or Office 2024+)
A QuickBooks developer account
Basic redirect web page to handle authentication

Once that’s in place, you can do a lot with it.

	Richard Rost @Reply 5 months ago
As a reminder, folks, posting your contact information in the Forums is against the site Rules. I have many reasons why. If you'd like to be listed on the Access Developer Network page, then I'd be happy to list you there and you can direct people to that page.

Aleece Landis OP @Reply

5 months ago

Abraham Thanks for the comment, however, I don't need that level of complexity.  I'm trying to QuitBooks.

Joe  I'm testing out AccountEdge (mainly because I think it might allow me to quickly set it up to at least give me access to look back into historical data from my Quickbooks company file since later this spring I will loose the ability to use Quickbooks to even look at my company file.)  I am NOT going to continue my subscription.  So I have a drop dead date.

I think I'm still planning to create my own system in Access.  But AccountEdge software subscription is much better price ($240 a year) if I don't manage to get Access up an running in time.

Aleece Landis OP @Reply

5 months ago

So Again, My Original Post Question is:

When designing a small business financial database (in order to QuitBooks; Quit Quickbooks)
What are some tips and recommendations from a Book keeping Accounting point of view to avoid pitfalls and make life easier.

My plan of attack starts with importing lists as tables.

Then I expect I need to go through and make sure each table has what it needs in the format it needs. Make sure they have the appropriate primary key and set up my relationships.

Then I need to build Query, Forms, and Reports
Priority for me will be setting up my invoices and manual transaction entry.
Then I need to work on systems with VBA to help me import transactions from csv files for each different source (Payroll IIF, Amazon, ebay, paypal, banks, credit cards) and let me reconcile to make sure everything matches and is good.

Aleece Landis OP @Reply

5 months ago

FYI I am more than half way through all the beginner courses, however I believe I'm a little more advanced in my understanding about how it all works so the courses are really just filling in key details since I already have some experience writing VBA in Excel or using the open office version to write stuff for calc and using Python to create programs to edit IIF files for import into QuickBooks.

So, I really am looking for the financial database specific tips here. Or Point me to a place that caters to those kind of tips and isn't simply an article trying to sell a particular software service product.

	Aleece Landis OP @Reply 5 months ago
I am determined to do it myself. Not looking for some one to do it for me. Now if there is anyone who has done it who wants to discuss or help teach, please chat here or point me to a good place for those kinds of discussions.

Joe Holland @Reply

5 months ago

This is a fairly big task. Start by watching these videos.

Double-Entry Accounting: https://599cd.com/blog/display-article.asp?ID=2681
Account Balances: https://599cd.com/blog/display-article.asp?ID=2789
A/R: https://599cd.com/blog/display-article.asp?ID=1667
A/P Seminar: https://599cd.com/blog/display-article.asp?ID=2142

Also search YouTube for "How to Create Accounting Database in MS Access"

	Alex Hedley @Reply 5 months ago
Build up a set of requirements and break them down on paper first

Richard Rost @Reply

5 months ago

As I always mention in videos where I cover financial topics, I am not an accountant. So if you're looking for formal financial or accounting advice, I'm not the right person for that. What I can do is show you how to build accounting-related tools in Microsoft Access based on what I've actually used over the past 30+ years as a business owner and consultant. Every now and then, a real accounting professional will chime in and say, "That's not how you're supposed to do it." And my response is usually, "Fair enough, but it is how I've been doing it, and it's worked for me."

	Aleece Landis OP @Reply 4 months ago
Anyone have recommendations on how to clean up the ChartOfAccounts before I import into Access? Fields I should add/remove? here are the fields I get from my export out of QB:

	Aleece Landis OP @Reply 4 months ago
Aleece AccountListID AccountName AccountFullName AccountIsActive ParentRefFullName Sublevel AccountType SpecialAccountType AccountNumber Desc Balance TotalBalance TimeCreated TimeModified SalesID SalesRefFullName LineId LineName urrencyRef

Aleece Landis OP @Reply

4 months ago

I'm thinking the last 5 fields are empty so will leave them out since I didn't use them in QB
The Time Created and Time Modified fields probably don't apply so should leave them out
And the AccountListID from QB Probably doesn't apply anymore once I bring it over but not sure if I should leave it to help me sort things out later (perhaps name it QBAccountListID)

Should I stick with the nested accounts with sublevel and parent Ref stuff or should I flatten out my account list?

Should I fix the AccountNumber scheme (I was not very good about how everything got set up in QB because things grew and got added just to sort things out and QB changed over the years etc.)

	Joe Holland @Reply 4 months ago
Sounds like you have good ideas on what to leave out. Keep the old accountlistid for reference to each transaction. Clean up accounts in Qbooks first. It's up to you if you want to flatten out the accounts. Trust your instincts. You can do a trial export and import once you make choices and see if you can make it work. You can always make changes and export again.

	Garry Smith @Reply 4 months ago
It would not hurt to bring over any fields that have data in them in the beginning. You can always remove them from your Access database in the future if you decide you do not need them, especially the Time Created and Time Modified. These fields have helped me in the past to find records when something may have gone sideways.

Aleece Landis OP @Reply

4 months ago

One thing I wish were easier in QB was to find accounts that have never been used (short of going through each account in the list and doing a quick report and then changing the date to all which is quite a few steps for each account in the list. And then there is keeping track of where I left off when I get called away from the computer, which often happens about every 10 minutes here.)
I'm thinking I will flatten things out then.

	Joe Holland @Reply 4 months ago
Aleece To find unused accounts in QuickBooks Desktop, go to the List menu > Chart of Accounts, click the Gear icon (Settings) and select Include inactive, then sort by Balance to find zero-balance accounts, or run reports like a Transaction Detail by Account report and filter for zero activity.

	Aleece Landis OP @Reply 4 months ago
Joe 0 balance doesn't equal 0 activity. I haven't figured out how to filter for zero activity

	Joe Holland @Reply 4 months ago
Aleece How about running a trial balance report (or any other report) including all dates. Compare the Chart of Accounts to the accounts listed on the report. The missing accounts should be the ones that were unused.

	Joe Holland @Reply 4 months ago
Aleece another option is to try and delete accounts. If they are used in transactions, QuickBooks will warn you that it cannot delete the account because it has a balance or is used in at least one transaction.

	Aleece Landis OP @Reply 4 months ago
Joe yep, have used that trick.

	Garry Smith @Reply 4 months ago
Aleece I tried to post my email but AI refused it. I don't know how one can reach another user on this forum anymore.

	Donald Blackwell @Reply 4 months ago
No Sharing of Contact Information. See Rule #21. The only exceptions are the Access Developer Network and the Access Template Marketplace

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View