• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 328
  • Last Modified:

Altering a MSSQL table from Microsoft Access via DAO

I have a support database in Access that I've used for years to alter the structure of a MSSQL database. Up until now, the code used ADO to do that but, thanks to some internal IT changes at my client, ADO is no longer allowed. They are insisting on DAO.

Here's the original ADO code. In this example, it is changing the length of field PONumber in table tblOrders to 20.


Private Sub cmdAlter_Click()
Dim strSQL As String
Dim oConn As ADODB.Connection

    Set oConn = New ADODB.Connection
        oConn.Open "Driver={SQL Server};" & _
                   "Server=" & Me.servername & ";" & _
                   "Database=" & Me.databasename & ";" & _
                   "Uid=" & Me.username & ";" & _
                   "Pwd=" & Me.password & ""

    strSQL = "ALTER TABLE dbo.tblOrders ALTER COLUMN PONumber nvarchar(20)";"
    oConn.Execute strSQL


End Sub


Can the same thing be done in DAO?


1 Solution
Why not use a pass-through query?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You can try this:

Dim db As DAO.Database
Set db=DAO.OpenDatabase("", False, False,"ODBC;DRIVER={SQL

I've not tried this in YEARS.

Why in the world would your customer not use ADO with SQL Server? DAO is generally slower when working with server based data ...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now