Changing Field properties in an Access DBTable

[Talat]

Hi,

I need to change the properties of a couple of databse fields in a table. I am using Office 2003 Access application. Its a filed in a linked table wrongly set to TEXT and it should be CURRENCY. After researching teh web I tried to use the following code but ist giving me the following error message.

"Run time Error '3293' - Syntax Error in ALTER TABLE Statement"


This is the code I am running:

Private Sub Command33_Click()


Dim strDB As String, MySQL As String

Dim appAccess As Access.Application

Const strConPathToExtDB = "C:\Users\Talât\Desktop\TestDB.mdb"

Set appAccess = CreateObject("Access.Application")

appAccess.OpenCurrentDatabase strConPathToExtDB


MySQL = "ALTER TABLE Ltbl_Products ALTER COLUMN 'Unit Cost' CURRENCY (2);"

appAccess.DoCmd.RunSQL MySQL

appAccess.CloseCurrentDatabase
Set appAccess = Nothing

End Sub

Can anyone help out please?

Thanks.

Talât

[tkorsano]

hi there,

in sql, single quotes denote a string, which could be type ntext, varchar, etc.
it is used a lot in string comparisons.

therefore, i am suggesting to try:

"ALTER TABLE Ltbl_Products ALTER COLUMN [Unit Cost] CURRENCY (2);"

instead of

"ALTER TABLE Ltbl_Products ALTER COLUMN 'Unit Cost' CURRENCY (2);"

moreover, check if your access version supports "alter column" statemente because some access versions don't support such statement in its syntax.

good luck,

tonci korsano

[Hack]

And this would be a moot issue if you did not have a space in the field name.

[Talat]

hi there,

in sql, single quotes denote a string, which could be type ntext, varchar, etc.
it is used a lot in string comparisons.

therefore, i am suggesting to try:

"ALTER TABLE Ltbl_Products ALTER COLUMN [Unit Cost] CURRENCY (2);"

instead of

"ALTER TABLE Ltbl_Products ALTER COLUMN 'Unit Cost' CURRENCY (2);"

moreover, check if your access version supports "alter column" statemente because some access versions don't support such statement in its syntax.

good luck,

tonci korsano

Thanks Tonci,

I tried it with [Unit Cost] and I still get the same error message, so may be that my access version does not support Alter Table statement. How do I check if this is so? Is there an another statement I can use?

There must be a way to do this...

[Hack]

ALTER TABLE is supported by Access 2003

http://office.microsoft.com/en-us/ac...001032207.aspx

[tkorsano]

hi there,

in the help menu of access ( as well as many other products for windows ) there is an about option, which will show what access version you are running.

the problem is that "alter table" syntax is supported, and at the same time "alter column" might not be supported depending on access version.

i am starting to think that in access currency data type shouldn't be specified with precision, which is what you are trying to do. in sql server, money data type by default has 4 decimal places, so currency data type in access might be doing the same approach.

there is also an ado approach at changing a column data type in access, like i am showing:

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data Source=D:\YourDatabase.mdb"
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
'cat.Tables("Ltbl_Products").Columns("Unit Cost").Name = "Unit Cost"
cat.Tables("Ltbl_Products").Columns("Unit Cost").Type = Currency

however, before you embark yourself into using this coding approach, try first

"ALTER TABLE Ltbl_Products ALTER COLUMN [Unit Cost] CURRENCY;"

good luck and please tell me how it goes,

tonci korsano

[Talat]

ALTER TABLE is supported by Access 2003

http://office.microsoft.com/en-us/ac...001032207.aspx

Thanks hack.

It is MS Access 2003 I am using. I will also see if I can cnage teh column head in eth excell file so teh linked table field name does not have a space ; ie its something like UnitCost and let you know. I am trying out a few suggestions that came from this forum.

Talât

[Talat]

hi there,

in the help menu of access ( as well as many other products for windows ) there is an about option, which will show what access version you are running.

the problem is that "alter table" syntax is supported, and at the same time "alter column" might not be supported depending on access version.

i am starting to think that in access currency data type shouldn't be specified with precision, which is what you are trying to do. in sql server, money data type by default has 4 decimal places, so currency data type in access might be doing the same approach.

there is also an ado approach at changing a column data type in access, like i am showing:

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data Source=D:\YourDatabase.mdb"
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
'cat.Tables("Ltbl_Products").Columns("Unit Cost").Name = "Unit Cost"
cat.Tables("Ltbl_Products").Columns("Unit Cost").Type = Currency

however, before you embark yourself into using this coding approach, try first

"ALTER TABLE Ltbl_Products ALTER COLUMN [Unit Cost] CURRENCY;"

good luck and please tell me how it goes,

tonci korsano

Hi Tonci,

I tried
"ALTER TABLE Ltbl_Products ALTER COLUMN [Unit Cost] CURRENCY;"
and now I get

"Run-time Error 3611 - Cannot execute data definition statements on linked data sources"


To use the second approach you suggested, where do I place the code please?

Thanks.

Talât

[tkorsano]

i dont really know about access other than basics, but somebody has a solution for this problem. it is here --> http://www.pcreview.co.uk/forums/thread-2622614.php

you are working with a linked database and most likely linked tables. what is your linked database. years ago i saw people linking oracle rdb tables to access 97. if the database you are linking is not access then that alter statement might not work. depending on what is the type of your linked database, you can issue what is called sql pass through queries, meaning sql which is native to that linked database.

to try the other code i sent you, you would comment the current code you have for your alter column statement, and copy the one i sent you, like this:

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data Source=<physical path of your access database, only if this is an access database>"
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
cat.Tables("Ltbl_Products").Columns("Unit Cost").Name = "Unit Cost"
cat.Tables("Ltbl_Products").Columns("Unit Cost").Type = Currency

good luck,

tonci korsano

[Talat]

Thanks Tonci.

Not much progress, I am afraid. I ahve put the following code in form click event

Private Sub Command33_Click()

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;data Source=C:\Users\Talât\Desktop\TestDB.mdb"
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog
Set cat.ActiveConnection = cn
cat.Tables("Ltbl_Products").Columns("Unit Cost").Name = "Unit Cost"
cat.Tables("Ltbl_Products").Columns("Unit Cost").Type = Currency
End Sub

But I am getting compile error at line

Dim cat As ADOX.Catalog
and also iy does not like the last line before end Sub statementm namely

cat.Tables("Ltbl_Products").Columns("Unit Cost").Type = Currency

It comes up in red font.

The database is an Access database and it has two excell files as linked tables. These excell files are live files, data being added to them regularly and are also used in some other applications. So I cannot import them as Access tables.

On the solution to runtime error 3611 you pointed me to, I am afraid I do not understand when teh author says "You will need to OpenDatabase on the back end mdb, and Execute the DDL query
statement on that db." Seems I am missing something crucial point here.

Sorry.

I tried re-linking the table after making sure that in teh excel file the column in question is set to format Currency, but when I link the table still comes across as a text.

Are you still able to persevere with me on this one?

Thanks.

Talât

[Talat]

I am giving up on trying to change the field properties of teh linked table now using SQL or any other method.

I will start another thread to see if I can have suggestion as to how I can re-link the excel file to Access database and ensure that teh column in question ends up having teh "currency" or "number" properties as opposed to text.

Thanks to all those who tried to help with teh previous thread.

PS. Admin- Apologies in advance if you find teh new thread somewhere else. I am desparate to get this project moving and elsewhere posted has not produced any answers yet.

Talât