Two MS Access VBA Questions

[dataferret]

Hi all
I am using VBA to create tables in MS Access. However I have two problems and I cannot seem to find answers. Hopefully someone here may be able to help.

Problem 1)
I created a table called "counter" using VBA. When I am finished I want to delete the table. My code is identical and works to delete the other tables except for the table name but the counter table refuses to delete.
Code:
' Delete the counter table
Public Function DeleteCounterTable()
CurrentDb.Execute "Drop Table counter;"
End Function

The counter table has the following columns:
startdate, counter

The layout of the table is dictated by another application and cannot be changed. I have no idea why this table will not delete.

Problem 2)
I created a table with fields which require date/time but when I import the date and time values the field shows #Error?

I am using the following code to create the field:
Code:
Set fld = tdf.CreateField("start_date", dbDate)
tdf.Fields.Append fld

The date/time data I am trying to import is:
0001-01-01 00:00:00

MSAccess does not recognise dbDateTime as a valid argument.

I know it is a long shot but I would appreciate any suggestions.

Thanks

 

[Leo-InstallingIT]

Hi

For problem 1, you should probably rename the table, as counter is reserved word in access/vba.


Problem 2, could you explain where dbDateTime comes in please?

I hope this helps,
Many Thanks

Leo

 

[Walk-IT]

Problem 1:

'counter' is a reserved word in MS access, I suspect that it is getting confused when you run the command, and does not know what counter you are referring to.

Try renaming your table to 'tblCounter' and try again!
The naming prefix is a standard practice you should adopt, as it helps prevent confusion and coding errors.

Problem 2:

"MSAccess does not recognise dbDateTime as a valid argument."
Is that the error message you are receiving from access?

 

[Walk-IT]

Look what happens if you go for a cuppa mid-post! Someone beats you to it!

 

[dataferret]

Hi Leo
Thanks for your interest in this.

For problem 1, you should probably rename the table, as counter is reserved word in access/vba.

I need the table created as counter because this is dictated by an external system and data is imported/exported to/from it. If it is possible to rename the table prior to deleting it this would solve the problem. I will give it a try and let you know if it works.

Problem 2, could you explain where dbDateTime comes in please?

I tried replacing dbDate in my code with dbDateTime thinking it would work for date and time strings but of course it does not. So I am stuck

Also Thanks to Walk-IT - you must have posted just as I was typing the response.

Unfortunately the table names are dictated by an external database and the naming convention is not of my choosing.

The exact Error message when I use dbDateTime instead of dbDate to create the field is:
Runtime Error 3421
Data Type Conversion Error

 

[dataferret]

Unfortunately it seems Access wants to be stubborn and refuses to rename the table once it has been created and named counter.

This code does not work for renaming the counter table:
CurrentDb.Execute "ALTER TABLE counter RENAME tblcounter;"

I am not sure if you noticed I edited my last post to add extra information so I have put it here too:

"...Thanks to Walk-IT - you must have posted just as I was typing the response...."

Unfortunately the table names are dictated by an external database and the naming convention is not of my choosing.

The exact Error message when I use dbDateTime instead of dbDate to create the field is:
Runtime Error 3421
Data Type Conversion Error

 

[Walk-IT]

hmmm, the rename command isn't going to understand which counter you are trying to use either.

Try this: CurrentDb.Execute "Drop Table [counter];"

The square brackets indicate the name of the object.

 

[dataferret]

Try this: CurrentDb.Execute "Drop Table [counter];"

The square brackets indicate the name of the object.

Worked a treat. Thanks so much. I have been scratching my head on this for days.

Do you have any suggestions regarding problem 2 and the date/time mismatch?

I appreciate your help so thanks again.