Basic starting point question

[xredvette6]

I have built a few Access database programs for desktop use that did not require a live feed of data. However, I'm in uncharted waters now and need some basic help as I have very little knowledge about servers. I am looking to set up a database that will need to utilized MS Office 365 as I will have several users in various parts of the country. The database will need to be able to have its main table appended with new records which will be generated by another company and sent to my company via ftp server.



I have a couple questions that I need to find answers for very quickly.

1. Can Access 2010 be set to automatically import the data feed or will it need to be done manually?
2. To accomplish the above will I need to incorporate SharePoint and/or some other type of SQL server?

I have started the design of the data base and have to say I have struggled a bit with the fact a web-based Access data base has to use macros for everything.

Please point me in the right direction. Thank you for your help.

[rpeare]

First and foremost, after you import your data, who is your intended audience for the collected data. Is it each one of the sites that the data comes from? or will the data only be used locally (at your location).

If the data itself is only going to be used locally you don't need to build a web database at all.

There are ways to automatically import data but your process would really rely on whether or not you have to use a web database or not. The worst case scenario is if you DO have to use a web database (your data needs to be accessible to people not on your local network) you'd have to have the file be named the same every time which can lead to confusion over time unless you're diligent about renaming files.

If you DO have to use a web database (the external sites need to have access to the data) you probably can do away with the file import altogether and have them do their data entry through your web database. That would avoid the complication altogether.

[xredvette6]

I will have people in different states that would need to have access to the data. The import data is being created by a company that is receiving products and issuing credits to customers. They then send me files of each unit requiring repair, so the data consists of individual repair inventory records which will be imported to my database for tracking of repairs, statuses, part orders and usage in the repair process, and other production reporting needs.

[rpeare]

So which part do you want help with, the actual importing of the excel file? if so what level of automation are you looking for, a one button click to import any current information? something that will run on it's own? what's going to be your trigger to know when a new file is available to upload? Do you have a log of files that have been uploaded via your FTP site? Is that log a SQL table? probably need a lot more information about your actual manual process as it exists right now to give you some direction.

[xredvette6]

The file/s will be delimited text files and will be pushed to an ftp server every 15 or 20 minutes. Each item will have its own unique txt file with unique file names. I would like to have a scheduled routine run and grab the files on the ftp server every 20 to 30 minutes, batch them if necessary, and then import them into Access to append the current table with the new records.

I will be using Office 365 with SharePoint (never used this before) and assume I will get some sort of help setting this up; however, I was hoping to gain a better understanding as quickly as possible. I only have two weeks to get this in place.

[rpeare]

I don't believe you'll be able to do this in a web database, you will have to have something running in the background (either a PC that's no being used by anyone else or have a SQL job running)

Here's what I would do if I'm just stuck with Access as the only tool.

Create your web database, see if you can get the lookups you want working the way you want.

If you can then link your tables to a NON web database. If you can you can run a module in the NON web database using filesystemobject commands to cycle through files in a folder (assuming all your ftp uploads are going to the same folder, but filesystemobject can handle it if you're not).

Here's an example of cycling through files in a folder using filesystemobject:

http://www.4guysfromrolla.com/webtec...ect/faq5.shtml

I don't know what your text files look like (content) but if they are in a flat file format you can use the docmd.transferspreadsheet or docmd.transfertext commands to import the data into your Linked Access tables.

Doing it on a timer is tricky because what happens when you are checking a timer is that every millisecond your computer is involved in checking to see if the time of your PC meets a specific criteria and it can really bog things down. There's no good way to say 'every 20 minutes do this' in access alone. However if you have a database that, when you open it, performs all your imports systematically, then you can use your task scheduler to open that database and in the ON OPEN event of your main form (one that loads when the database is opened) you can run your procedure and close the database every 20 minutes.

There are, of course other options but if Access is your only tool this is the only thing I can think to do.

If you're using SQL and Powershell you can likely build a script and a SQL job that'll run every 20 minutes with no problem.