Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    I want to run a DDL query to add a field to a table in a SQL server database from VBA using a DSN but I can't figure out how to run it. This is what I have so far -

    Dim cn As ADODB.Connection
    Set cn = New ADODB.Connection
    cn.Open "dsn=MyData"
    strSQL = "ALTER TABLE Customer ADD UploadTime SMALLDATETIME"


    so how do I execute this statement against the connection?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd change the SQL to

    strSQL = "ALTER TABLE Customer ADD COLUMN UploadTime SMALLDATETIME"

    You can execute it by using the Execute method of the connection:

    cn.Execute strSQL

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Thanks Hans - really easy when you know!
    It didn't like the 'COLUMN'

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='kentg' post='776036' date='20-May-2009 09:04']It didn't like the 'COLUMN'[/quote]
    Apparently the SQL Server version of SQL is slightly different from that in Access.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Brisbane, Queensland, Australia
    Posts
    387
    Thanks
    0
    Thanked 1 Time in 1 Post
    Next probem - I can't see the changes in Access until I delete and reattach.
    any suggestions on how to refresh the link?
    or do I need to drop the connection and then transferdatabase aclink to reattach?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    It's probably best to delete and recreate the linked table.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •