Running DDL on a SQL backend

Topic

New Reply

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?

Reply | Quote

Replies

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

Reply | Quote

Thanks Hans – really easy when you know!
It didn’t like the ‘COLUMN’

Reply | Quote

It didn’t like the ‘COLUMN’

Apparently the SQL Server version of SQL is slightly different from that in Access.

Reply | Quote

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?

Reply | Quote

It’s probably best to delete and recreate the linked table.

Reply | Quote