Create table (access 2000)

Hi, I am trying to create a table in VBA code. I checked msdn and googled a little bit and got some chunks I thought would work, but I get a data type conversion error. Any pointing in the right direction would be much appreciated.

dbText instead of Text as type got me past that point. Heh

You seem to have found the answer to your original question, but your question raises an issue to me: why would you want to create a new table (with just one field) in a VBA module? The reason I'm asking is that this is a huge red flag to an experienced developer. Modules are written to do repetitive operations, suggesting that maybe you're creating a new table frequently, which in turn suggests that you may be doing something quite unnecessary and even counterproductive. I'm going to guess that you're creating such tables to export for some other use, but that's rarely, if ever, needed. In any case, you could probably do that with a Make Table query. Possibly you have a valid reason that isn't apparent to me, but as I said, the fact that you're trying to do it at all raises a red flag to me.

Well it is a table used to hold data imported from a CSV. That info will be compared, updated and exported to a new csv daily. Then the table becomes unnecessary.

I figured I'd use a module because there is a lot existing code where this happens (we import 6 csv files daily, not to the same table).

What would you suggest is the better method normally?

So you import 6 CSV files daily, do some manipulation, then export them back to CSV files? Why?

OK, then you're actually using Access as a data processing tool, not as a database. I've done that. As a database kinda guy, it pains me to do that, because it suggests that the entire operation ought to be a database application, but I admit that situations exist where there is no value at all in retaining the data, so I would not try to insist that it shouldn't be done.

So your solution may be quite reasonable. You might find it just as easy to use a Make Table query and just call it from your code, but there's nothing fundamentally wrong with doing it the way you are. I take it you solved your original problem?

@AOG123: You were probably too young, when I was teaching database classes!

well we use Access, but our web hosting service wont allow for odbc (i think that's it, the thing that lets ACCESS and SQL talk).

So we need to export from web SQL, import to ACCESS, update shipping information on the supplied orders, export back to CSV and import back to web SQL.

That's the one I'm working on now. The others (done before I came on board) are supplied from us by call centers with no access to our internal database.

Before me the company never used VBA so we have a ton of queries that do one thing, so I'm trying to code and avoid clogging our already overly sized database (hindering at about 1.7Gigs all the time)

OK. The Access Jet engine is perfectly happy with SQL (the language). ODBC is what allows Access to connect to non-Microsoft databases like Oracle, MySQL, etc. I'm not sure what "web SQL" implies, but I see your problem. There might be a general solution to such a problem, but it would be a bear to tackle. So your approach is probably a practical solution.

You are aware, I assume, of the 2 Gig limitation on an Access mdb. And do you regularly perform Compact/Repairs? That's the big problem with creating temp tables. Every time you create a new one, it requires more storage, even if you delete the old one! That's why temp tables are to be avoided like the plague, if you can possibly do so. If you can't, you have to perform frequent Compact/Repairs. That can be automated by doing it each time the mdb is opened (or closed, as I recall), although that can be annoying if you're trying to quickly open it to check something, and you have to wait for it to complete the C/R.

Anyway, you appear to have a valid purpose in creating these temp tables, in your operating circumstances. But more often than not, when I see someone creating such tables, it's because they're trying to do something the wrong way. That's why I pursued the point.

Hrm, yah we even use a third party product to compact repair since we are normally going over the 2Gigs and performance is an ongoing issue. I've tried convincing to migrate to SQL back end and VB front end but suits always know better than us and they're happy with the constant battles

I wasn't aware that Temp tables made it more of a mess, since there is no auto-number maybe it makes more sense just to delete everything from a constant table at the end. Or does that make a mess too? Keep in mind we do use temp tables for all the CSV mentioned in above posts.

That won't help. Once an object has been allocated space, it is not overwritten (re-used) until you run Compact. Thus, record deletions are just like table deletions, and every time you modify and re-save a form or report or query, the mdb grows bigger. Before you do the next Compact, check the file size, then run your Compact utility and immediately check the file size again. You might consider migrating to MS SQL and just using Access as a front end, which usually works, often with some adjustments. But if your management just doesn't want to do it, I understand the problem.