-
Changing Field properties in an Access DBTable
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
-
maybe the problem are single quotes
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
-
And this would be a moot issue if you did not have a space in the field name.
-
 Originally Posted by 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
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...
-
-
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
-
 Originally Posted by Hack
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
-
 Originally Posted by 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
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
-
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
-
Thanks Tonci.
Not much progress, I am afraid. I ahve put the following code in form click event
HTML Code:
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
-
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
Similar Threads
-
By Keiron in forum VB Classic
Replies: 1
Last Post: 10-28-2002, 09:49 AM
-
By Jeff in forum VB Classic
Replies: 7
Last Post: 03-30-2001, 09:15 PM
-
By Jeremiah in forum VB Classic
Replies: 2
Last Post: 03-07-2001, 09:18 PM
-
By Todd B - Agendum Software in forum vb.announcements
Replies: 0
Last Post: 12-20-2000, 01:22 PM
-
By Pädi in forum VB Classic
Replies: 1
Last Post: 08-23-2000, 11:10 PM
Tags for this Thread
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Development Centers
-- Android Development Center
-- Cloud Development Project Center
-- HTML5 Development Center
-- Windows Mobile Development Center
|