• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 326
  • 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

oConn.Close

End Sub

-------------------------------------------------------

Can the same thing be done in DAO?

Thanks!

James
0
jrmcanada2
Asked:
jrmcanada2
1 Solution
 
jerryb30Commented:
Why not use a pass-through query?
0
 
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
SERVER};ADDRESS=ServerAddress;SERVER=ServerName;WSID=PC;DATABASE=DatabaseNameB;TRUSTED_CONNECTION=YES;UID=UserName;PWD=Password;NETWORK=DBMSSOCN")

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 ...
0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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