I'm setting up an access database to use for creating proposals for our Home Automation clients. I'm currently doing it with an excel spreadsheet, but it is very clearly an Access application. Before I get myself into too much trouble I wanted to get some wisdom from those of you with way more experience (and way more recent experience... I was a pretty good Paradox programmer in the 80's...when there were only about 10 functions available... I realize some of you weren't even here in the 80's)
A quick background on where users and data will are located. We interface with Quickbooks data. Our QB file is located on our server. We have a small number of users (5) that will need to run the access application. It will be run from laptops that are on the network at work as well as over VPN (we are currently using hamachi for VPN... has worked spectaculary for keeping files synced between laptops and server in case you haven't tried it).
Ideally a person can work from home or the office and log into the access application. A "nice to have", but secondary consideration, would be the ability to run the application when you are not on line... say at the beach or on an airplane.... or at the beach.
The main lists we use from QB are the item, itemgroup and client lists (QB list = Access table).
During my preliminary testing I set up an ODBC link with qODBC. I looked at a lot of the other linking tools and this one seemed to be the closest and easiest for what I believe I need. So I made a linked item table in access to use for selecting items. The problem was that if I didn't have the connection to QB live, I couldn't open and use the table. So I thought if I created a linked table, but ran a query on the linked table to give me an "Item" table that was not linked, I could use that table to choose items for the project. This also had the benefit of striping out a lot of the extra QB fields I didn't need. Seems like it worked, but I'm not sure what kind of trouble I might get myself into down the road.
So here are my initial questions (as you can see, I'm cleverly setting you up for the many, many questions that will come to follow. I have till Jan 1st to finish this project, but I don't have $10 for you... sorry but I couldn't resist)
QB is running on the server - should I split access in to a FE and BE and run the BE on the server with QB?
Is it likely my query to a linked table will work for the items, groups and client lists in QB? I will still have to use the actual linked table in order to add and update QB items and clients but since those type of changes are much less frequent than "choosing" items from the item table I dont think that will be a problem
A quick background on where users and data will are located. We interface with Quickbooks data. Our QB file is located on our server. We have a small number of users (5) that will need to run the access application. It will be run from laptops that are on the network at work as well as over VPN (we are currently using hamachi for VPN... has worked spectaculary for keeping files synced between laptops and server in case you haven't tried it).
Ideally a person can work from home or the office and log into the access application. A "nice to have", but secondary consideration, would be the ability to run the application when you are not on line... say at the beach or on an airplane.... or at the beach.
The main lists we use from QB are the item, itemgroup and client lists (QB list = Access table).
During my preliminary testing I set up an ODBC link with qODBC. I looked at a lot of the other linking tools and this one seemed to be the closest and easiest for what I believe I need. So I made a linked item table in access to use for selecting items. The problem was that if I didn't have the connection to QB live, I couldn't open and use the table. So I thought if I created a linked table, but ran a query on the linked table to give me an "Item" table that was not linked, I could use that table to choose items for the project. This also had the benefit of striping out a lot of the extra QB fields I didn't need. Seems like it worked, but I'm not sure what kind of trouble I might get myself into down the road.
So here are my initial questions (as you can see, I'm cleverly setting you up for the many, many questions that will come to follow. I have till Jan 1st to finish this project, but I don't have $10 for you... sorry but I couldn't resist)
QB is running on the server - should I split access in to a FE and BE and run the BE on the server with QB?
Is it likely my query to a linked table will work for the items, groups and client lists in QB? I will still have to use the actual linked table in order to add and update QB items and clients but since those type of changes are much less frequent than "choosing" items from the item table I dont think that will be a problem
