Completely New To Access, Need advice

[zoog25]

Hello all,

I need help getting started with access. Here is the situation. A while back we had an employee who developed an access program that would look at pdf drawing files that were transfered to folder a and would then check the appropriate job fold located on our server. If the drawing names matched then it would replace those drawings.

The file names follow this structure "xxx-xx" there the left side of the "-" is the drawing number and the right side is the version.

I want to try my best to recreate that program but i'm not familiar with access. I do know how to do excel programming, but I want try working with excel on this since it worked before.

Anyways any advice as to where I can look for info to start or anyone can help. That would be great.

 

[Gideon1973]

On the surface, I would say there are a handful of different "components" and skill-sets you will put together to write that type of program in Access/VBA:

1) You need to learn how to read files from the file system. I typically use the FileSystemObject library in VBA. You can use that object to loop through all files in a particular folder, and then take action depending on the file name. More info on the FileSystemObject library may be found at: Accessing Files with FileSystemObject

2) You will also need to know how to update data from a database using DAO. Here's a good starting reference: How to: Modify an Existing Record in a DAO Recordset [Access 2007 Developer Reference]

3) Understanding control flow constucts, such as IF...THEN, DO...WHILE, FOR...EACH, etc., will also be quite useful as you process each file: Chapter 2: The Basics of Writing and Testing VBA Code (Part 2 of 2)

4) Depending on how complex your updates are or if you need to reference data from the database as you are processing your files, you may need to brush up on some SQL syntax. Here's a good primer: SQL Introduction

5) Lastly, an understanding of table and form design will be helpful. Access help should get you started on this one.

Hope that helps!

 

[Joe4]

Before you start trying to design any kind of project that is more than just a simple task, the first thing you should do is make sure you understand the concepts of Relational Databases and the Rules of Normalization. If you do not design your database with these concepts in mind, you can quickly program yourself into a corner and cause yourself a lot of unnecessary grief (been there, done that).

Here is a good link on those topics: Fundamentals of Relational Database Design

 

[zoog25]

Thanks Joe4

 

[Gideon1973]

Before you start trying to design any kind of project that is more than just a simple task, the first thing you should do is make sure you understand the concepts of Relational Databases and the Rules of Normalization.

Great add!

 

[Patron-0855]

Hello all,

I have reasonable VBA skill, but none on Access. My question is more fundamental. Would I gain improvements in performance and functionality going this route? My Spreadsheets are becoming bulky. I have good performance once I have abstracted the data from the spreadsheet.

My project revolves around commodity futures and options strategy backtesting.