Changing Field properties in an Access DBTable


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 11 of 11

Thread: Changing Field properties in an Access DBTable

  1. #1
    Join Date
    Aug 2009
    Posts
    37

    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

  2. #2
    Join Date
    Oct 2008
    Posts
    142

    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

  3. #3
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    And this would be a moot issue if you did not have a space in the field name.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  4. #4
    Join Date
    Aug 2009
    Posts
    37

    Exclamation

    Quote Originally Posted by tkorsano View Post
    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...

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    ALTER TABLE is supported by Access 2003

    http://office.microsoft.com/en-us/ac...001032207.aspx
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  6. #6
    Join Date
    Oct 2008
    Posts
    142
    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

  7. #7
    Join Date
    Aug 2009
    Posts
    37
    Quote Originally Posted by Hack View Post
    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

  8. #8
    Join Date
    Aug 2009
    Posts
    37
    Quote Originally Posted by tkorsano View Post
    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

  9. #9
    Join Date
    Oct 2008
    Posts
    142
    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

  10. #10
    Join Date
    Aug 2009
    Posts
    37
    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

  11. #11
    Join Date
    Aug 2009
    Posts
    37
    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

  1. Edit Access file properties
    By Keiron in forum VB Classic
    Replies: 1
    Last Post: 10-28-2002, 09:49 AM
  2. Changing field names in a table through VBA
    By Jeff in forum VB Classic
    Replies: 7
    Last Post: 03-30-2001, 09:15 PM
  3. Access Date/Time Field
    By Jeremiah in forum VB Classic
    Replies: 2
    Last Post: 03-07-2001, 09:18 PM
  4. Replies: 0
    Last Post: 12-20-2000, 01:22 PM
  5. How to access properties Top and Left
    By Pädi in forum VB Classic
    Replies: 1
    Last Post: 08-24-2000, 12:10 AM

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
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center