Brendan, you have two distinct issues here.
One is that you need to IMPORT data. I'm convinced you can do that. It is just an issue of the mechanics of data flow. Eminently doable in many different ways.
The other issue is that you need to HOLD data safely in Access. This is tricker - but not because I doubt your abilities. It just takes some extra care because of your environment - i.e. some flavor of Windows.
Let's be honest - we're dealing with Windows, not noted as one of the more secure versions of operating system ever built. So you already have one strike against you. You didn't say whether you were on Windows 98, Windows me, WindowsNT, Windows 2K, or Windows XP, so that makes a difference. NT and 2K have a reasonable if not perfect security kernel. You would still need a good NT administrator to set up the protection, but it is at least feasible. For 98 on the other hand, it is time to laugh so hard as to bring tears to the eyes.
The implication of storing encoded or encrypted data in a table is that if you are going to be able to later compare, retrieve, or report it, you have to also be able to DECRYPT the data. Which means that Access needs to have both the encryption and decryption software modules available to it. So if someone steals your database, they also steal the keys. Unless... you split the database and very carefully hide the key in a third place.
So your problem in securing your database against intrusion is a multi-fold problem, requiring a multi-fold approach.
First, make sure that you are on an O/S that has NT-class (and I'm not talking NT 3.1 here, either) security or higher.
Second, consider storing the data separately from the rest of the database. I.e. Front-End, Back-End case. You DON'T want the cryptology code on the same computer as the data to be protected by that code. Because you MUST assume that the person who really wants to get to your data WILL get to it. So you have to make sure you don't do the moral equivalent of leaving the keys to the car under the floor mat.
Third, if it is at all possible, get a copy of some security encryption software that provides you a programming library. Then define a few references to that library, probably in a .DLL file. You can define public, external references in VBA and make them callable from queries, reports, forms, etc. But if it is external to the front-end file, all the better. Because the more places a hacker has to go, the more frustrated s/he might become before getting all the files needed to "crack the nut" and retrieve the data.
Fourth, consider using external, non-Access files to hold key strings. Perhaps using the VBA Open and Close commands to get to these external files. Make sure that the files are stored elsewhere from the .MDB files. Perhaps on other networked drives that have passwords required for entry when you boot up the system rather than having an automatic, no-password connection. Again, diversify the locations of what you store, and make sure that at least some of the key components (pun intended!
) are not with the rest of your data.
Fifth, and this is a conceptual thing, make sure that YOU are one of the persons whose credit card information is in that data base. If THAT won't make you paranoid enough to implement really careful security designs, nothing will.
Now, I'm assuming that you have to use Access. But there is a another reasonable and prudent approach if you have the money for it. ORACLE databases can be made to implement extra security when required. Other databases such as Sybase can also add value to this mix, security-wise. So if you have a way to make the data storage work through ODBC to a more secure storage methodology, that would help a LOT.
