May 14, 2015 at 2:46 pm
I have the following requirements and I would like to know the best manner to proceed:
1. Users will enter data into an Access 2013 front end gui application and the data will be initially stored in the Access 2013 database.
2. The data will then be loaded to a Sql Server 2012 database on a daily basis.
3. The data will be displayed in SSRS reports.
Thus can you tell me and/or point me to urls that will explain how to accomplish the requirements that I listed above?
May 14, 2015 at 2:55 pm
create a whole bunch of linked SQL Server tables in your Access database.
Create queries to append the local table data to the backend SQL table.
then you could do something like
DBEngine(0)(0).Execute "appendqueryname1"
DBEngine(0)(0).Execute "appendqueryname2"
etc.
May 15, 2015 at 8:25 am
I have the following questions to ask you based upon your answer since I have never used SSIS packages before:
1. Would you show me the sql on how to create 'a whole bunch of linked SQL Server tables in your Access database'?.
2. 'Create queries to append the local table data to the backend SQL table'? It seems like you are saying that I should create 1 local data table. However all the access database tables have different formats? How can I append different table formats to 1 table? Basically would you show me the sql on what you are exactly referring to?
May 15, 2015 at 12:32 pm
A linked table in Access is a table object which doesn't reside in Access, but exists somewhere else and is linked to the Access table objects. It sounds complicated but Access does most of the work.
1. Go to the menu External Data and make an ODBC link to your SQL Server database.
2. Then you use the Linked Table Manager to create an Access table object "linked" to the SQL Server tables you want. I'd give the Access linked tables a slightly different name, like this: If S/S table name is: Orders, you could make the linked Access table name Orders_acc.
Then play around with one of the linked tables a little (in a DEV environment of course) and do some selects, inserts, and deletes. You'll understand linked tables very easily that way.
You could have your Access application write all data changes directlyl into the SQL Server linked table. You won't have to do any hourly/daily uploads, BUT you also won't be able to do any validation, so your front-end app will have to do anything like that if you need it.
Read up on linking tables on the web. I'm sure MSDN has plenty of examples.
HTH,
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
May 15, 2015 at 12:34 pm
I forgot to add that pietlinden wasn't talking about using SSIS. If you have linked tables you won't need SSIS. It's very useful but has a bit of a learning curve.
Good luck!
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
May 16, 2015 at 4:11 pm
wendy elizabeth (5/14/2015)
I have the following requirements and I would like to know the best manner to proceed:1. Users will enter data into an Access 2013 front end gui application and the data will be initially stored in the Access 2013 database.
2. The data will then be loaded to a Sql Server 2012 database on a daily basis.
3. The data will be displayed in SSRS reports.
Thus can you tell me and/or point me to urls that will explain how to accomplish the requirements that I listed above?
Quick question, is the data entered off-line like an "on-the-road" salesman thing, and then synchronized?
😎
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply