Inserting New Field to SQLServer Back-end via MS Access Front-end

[satchi]

Hi,

I have recently converted an Access front/back-end app into a SQL Server (2000) back-end database from a front-end MS Access (2003). The tables are linked via ODBC.

I want to add a new field to one of the tables in the back-end, via the front-end Access by VBA coding.

I have done it in the past by creating a tabledef and appending a field to that, but now when I try that to the SQL server, it doesn't work. Basically it goes to setting the database with the workarea and it breaks there. "set dbData = wrkArea.OpenDatabase.... (below)

Is there a specific way to append to SQL Server? or see something wrong in my connection code? Thanks in advance.

Dim wrkArea As Workspace
Dim dbData As Database
Dim tbl As TableDef
Dim flds As Fields
Dim fld As Field
Dim strDest As String
 
strDest = GetPath("tblStudents")
 
Set wrkArea = DBEngine.CreateWorkspace("", "Admin", "sa", dbUseODBC)
Set dbData = wrkArea.OpenDatabase(strDest, True)
 
Set tbl = dbData.TableDefs("tblStudents")
 
Set fld = tbl.CreateField("Termination", dbDate)
tbl.Fields.Append fld
 
dbData.Close
wrkArea.Close

Where strDest = ODBC;DRIVER=SQL Server;SERVER=(local);....

[ck9663]

Hi,

I have recently converted an Access front/back-end app into a SQL Server (2000) back-end database from a front-end MS Access (2003). The tables are linked via ODBC.

I want to add a new field to one of the tables in the back-end, via the front-end Access by VBA coding.

I have done it in the past by creating a tabledef and appending a field to that, but now when I try that to the SQL server, it doesn't work. Basically it goes to setting the database with the workarea and it breaks there. "set dbData = wrkArea.OpenDatabase.... (below)

Is there a specific way to append to SQL Server? or see something wrong in my connection code? Thanks in advance.

Dim wrkArea As Workspace
Dim dbData As Database
Dim tbl As TableDef
Dim flds As Fields
Dim fld As Field
Dim strDest As String
 
strDest = GetPath("tblStudents")
 
Set wrkArea = DBEngine.CreateWorkspace("", "Admin", "sa", dbUseODBC)
Set dbData = wrkArea.OpenDatabase(strDest, True)
 
Set tbl = dbData.TableDefs("tblStudents")
 
Set fld = tbl.CreateField("Termination", dbDate)
tbl.Fields.Append fld
 
dbData.Close
wrkArea.Close

Where strDest = ODBC;DRIVER=SQL Server;SERVER=(local);....

am not 100% familiar with Access. there are a number of ways you can do this. one is to create a stored proc that do the insert. call that proc from your access using a recordset. your proc may return a value 1 or 0 for a successful or failed insert....

you may also build the transact sql (alter table...) as a string variable and execute it in a connection object...

[satchi]

Thanks for the reply ck9663,

I tried creating a connection and running a SQL statement through and it worked great! Thanks!

Dim wrkArea As Workspace
Dim conn As Connection
Dim dbData As Database
Dim tbl As TableDef
Dim strDest As String
Dim strSQL As String
 
strDest = "ODBC;DRIVER=SQL Server;DSN=FallStudents;SERVER=(local);Database=Students;Trusted_Connection=Yes"
strSQL = "ALTER TABLE tblStudentInfo ADD Termination varchar(30);"
 
Set wrkArea = CreateWorkspace("", "", "", dbUseODBC)
Set dbData = CurrentDb
Set tbl = dbData.TableDefs("tblStudentInfo")
Set conn = wrkArea.OpenConnection("NewConn", , False, strDest)
 
conn.Execute strSQL
 
tbl.RefreshLink
 
dbData.Close
wrkArea.Close