General Access questions - can or cannot

**Armitage2k** · 11-28-2011, 02:07 AM

Hi,

before I am jumping to creating endless amounts of threads about all the problems I will experience with access (which I am not really familiar with), I would like to get some feedback from the pros here if I am actually capable of achieving what I desire with Access or rather stick with Excel.

I am quite experience in working with Excel and writing VBA code specific for Excel, however, now I am in need of creating a database to keep track of approx. 35k+ clients and requires me to do the following actions preferably from within the database:

1. import delimited data from a textfile and update
The import step already works well since I wrote a piece of VBA for excel a long time ago and is supported by access. My challenge would be the update. Can or cannot access compare the client-table with the import-table (depending on a unique member ID) and only import NEW client data?

2. Collect client approval / data per email and update database automatically?
In case of changes of the terms and conditions, I need to get approval from the exisiting clients. I found a "collect data via email" feature in access 2007/2010 and that seems to cut it for me. However, can this feature generate an email with the clients name, ID, etc. (taken from the client table) within the email and then simply let the client choose between YES-NO and update the change in my database automatically? The database will be running on a server 24/11 with appropriated email setup in Outlook.

3. Use one form format to display data from several tables? (not at the same time)
Can access use one form format to display data from one table, but eventually also from another table? This means to me that I cannot clearly define the table when entering the data source of the form field. Can this somehow be variable? (in PHP I would think about launching an attribute along with the command to open the user form. get my point?)

4. Can I run excel functions in VBA from with access?
for example, how could I put this code below to work in access? Its a simple piece of vba for comparing data and deleting double existing one.

Code:

For x = lastrow To 2 Step -1
    If Application.WorksheetFunction.CountIf(Range("n2:n" & x), Range("n" & x).Text) > 1 Then
        Range("N" & x).EntireRow.Delete
    End If
Next x

particular interesting for me is how to address ranges in access?

5. in the distant future, in case this project is ever executed and runs in a stable manner, I would need to access the database on the server, from other locations at the same time. Is it possible to use the database as some sort of source and let a frontend connect to it and view data only?

5. The million dollar question: how high are the chances for me to get that done myself without wasting too much time? I am confident to get this job also done in Excel, but in case this really needs to be accessed from other locations, I latest then would need to port it to Access anywhere.

There actually is a capable IT Director working in my environment, but considering the amount of time it would take to create this, I would like to do as much as i can myself.

Looking forward to your feedback!
Thanks,

A2k

**jzwp11** · 11-28-2011, 11:47 AM

Here are my thoughts on your questions. Since it sounds like you want to make this accessible over the internet, you may want to look at my response to your question 5 first.

1. Access can be setup to import data from delimited text files. You will have to run an update query to change existing client records (with new data) and run an append query to add new client records.

2. I've never done this, but I think Access may be able to do this.

3. Yes & No. Forms in Access are typically tied to a specific table or query. However, Access does have the capability of using unbound forms (not tied to a table or query), but you would have to VBA to then append records to the appropriate table.

4. To manipulate data in Access, you would use queries and Access' built-in functions some of which are similar to Excel. In Access, you work with records not ranges. You also can create your own custom functions in Access. In VBA from Access, if you call the Excel application and are manipulating a spreadsheet, you would use Excel functions there but in general, Excel functions do not work in Access.

5a & 5b. Access is a desktop application; if your goal is to use this from multiple sites over a large geographic area then I would recommend going with SQL Server Express (free), MySQL or Oracle (I think they have a free version as well). This would be the backend (data tables) of your application. You would need to create a web-based front end (user interface). In Access 2010, you can do that but you need Sharepoint server in addition to Access. There are some companies out there that can take an Access application and make it web enabled--for a cost of course.