468,146 Members | 1,424 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,146 developers. It's quick & easy.

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

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

Expand|Select|Wrap|Line Numbers
  1. Dim wrkArea As Workspace
  2. Dim dbData As Database
  3. Dim tbl As TableDef
  4. Dim flds As Fields
  5. Dim fld As Field
  6. Dim strDest As String
  7.  
  8. strDest = GetPath("tblStudents")
  9.  
  10. Set wrkArea = DBEngine.CreateWorkspace("", "Admin", "sa", dbUseODBC)
  11. Set dbData = wrkArea.OpenDatabase(strDest, True)
  12.  
  13. Set tbl = dbData.TableDefs("tblStudents")
  14.  
  15. Set fld = tbl.CreateField("Termination", dbDate)
  16. tbl.Fields.Append fld
  17.  
  18. dbData.Close
  19. wrkArea.Close
Where strDest = ODBC;DRIVER=SQL Server;SERVER=(local);....
Aug 7 '07 #1
2 1503
ck9663
2,878 Expert 2GB
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.

Expand|Select|Wrap|Line Numbers
  1. Dim wrkArea As Workspace
  2. Dim dbData As Database
  3. Dim tbl As TableDef
  4. Dim flds As Fields
  5. Dim fld As Field
  6. Dim strDest As String
  7.  
  8. strDest = GetPath("tblStudents")
  9.  
  10. Set wrkArea = DBEngine.CreateWorkspace("", "Admin", "sa", dbUseODBC)
  11. Set dbData = wrkArea.OpenDatabase(strDest, True)
  12.  
  13. Set tbl = dbData.TableDefs("tblStudents")
  14.  
  15. Set fld = tbl.CreateField("Termination", dbDate)
  16. tbl.Fields.Append fld
  17.  
  18. dbData.Close
  19. 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...
Aug 7 '07 #2
satchi
5
Thanks for the reply ck9663,

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

Expand|Select|Wrap|Line Numbers
  1. Dim wrkArea As Workspace
  2. Dim conn As Connection
  3. Dim dbData As Database
  4. Dim tbl As TableDef
  5. Dim strDest As String
  6. Dim strSQL As String
  7.  
  8. strDest = "ODBC;DRIVER=SQL Server;DSN=FallStudents;SERVER=(local);Database=Students;Trusted_Connection=Yes"
  9. strSQL = "ALTER TABLE tblStudentInfo ADD Termination varchar(30);"
  10.  
  11. Set wrkArea = CreateWorkspace("", "", "", dbUseODBC)
  12. Set dbData = CurrentDb
  13. Set tbl = dbData.TableDefs("tblStudentInfo")
  14. Set conn = wrkArea.OpenConnection("NewConn", , False, strDest)
  15.  
  16. conn.Execute strSQL
  17.  
  18. tbl.RefreshLink
  19.  
  20. dbData.Close
  21. wrkArea.Close
Aug 8 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

6 posts views Thread by Bunty | last post: by
30 posts views Thread by didacticone | last post: by
1 post views Thread by gcdp | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.