Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Use of DAO In MS Access 2000 With a SQL Server 2000 DB

Newbie
  
Join Date: Jan 2007
Posts: 1
#1: Jan 9 '07
I have been looking after an MS Access database, using table links to
access data in a back-end MDB database. We have recently micrated to a
SQL Server 2000 back-end atabase, once again accessing it though table links.

I am comfortable with the data side of things (whether through table
links or an adodb.recordset). What I am missing, however, is all the
useful things you could do to automatically maintain a back-end database
from a front-end database via the DAO 'Database' Object which exposes
the TableDefs Collection, the TableDef Object, the Fields Collection, the
Field, the Indexes Collection and the Index object.

Is there any way I can, from an Access 2000 front-end database, connect
to a SQL Server 2000 database as a 'database' to perform the tasks in the
following procedures.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub SetBackEndDatabase(db As Database)
  3. Set db = _
  4. OpenDatabase("w:\backend\backend.mdb", False, False, ";PWD=" & "BackEndDatabasePassword")
  5. End Sub
  6.  
  7. Private Sub ModifyField(db, udtFieldProperties As FieldProps)
  8. Dim tdf As TableDef, fld As Field
  9. With udtFieldProperties
  10.   Set tdf = db.TableDefs(.TableName)
  11.   Set fld = tdf.Fields(.FieldName)
  12.   If .FieldType = dbText Then
  13.     fld.AllowZeroLength = .FieldAllowZeroLength
  14.   End If
  15.   fld.Required = .FieldRequired
  16. End With
  17. db.TableDefs.Refresh
  18. End Sub
  19.  
  20. Private Sub CreateIndexInExistingTable(db, udtFieldProperties As FieldProps)
  21. Dim tdf As TableDef
  22. Set tdf = db.TableDefs(udtFieldProperties.TableName)
  23. CreateIndex tdf, udtFieldProperties
  24. End Sub
  25.  
  26. Private Sub CreateFieldInExistingTable(db, udtFieldProperties As FieldProps)
  27. Dim tdf As TableDef
  28. Set tdf = db.TableDefs(udtFieldProperties.TableName)
  29. CreateField tdf, udtFieldProperties
  30. End Sub
  31.  
  32. Private Sub RenameField(db As Database, ByVal strTableName As String, ByVal _
  33. strOriginalFieldName As String, ByVal strNewFieldName As String)
  34. Dim tdf As TableDef, fld As Field
  35. Set tdf = db.TableDefs(strTableName)
  36. Set fld = tdf.Fields(strOriginalFieldName)
  37. fld.Name = strNewFieldName
  38. db.TableDefs.Refresh
  39. End Sub
  40.  
  41. Private Sub RenameIndex(db As Database, ByVal strTableName As String, ByVal _
  42. strOriginalIndexName As String, ByVal strNewIndexName As String)
  43. Dim tdf As TableDef, idx As Index
  44. Set tdf = db.TableDefs(strTableName)
  45. Set idx = tdf.Indexes(strOriginalIndexName)
  46. idx.Name = strNewIndexName
  47. db.TableDefs.Refresh
  48. End Sub
  49.  
  50. Private Sub RenameTable(db As Database, ByVal strOriginalTableName As String, _
  51. ByVal strNewTableName As String)
  52. Dim tdf As TableDef
  53. Set tdf = db.TableDefs(strOriginalTableName)
  54. tdf.Name = strNewTableName
  55. db.TableDefs.Refresh
  56. End Sub
  57.  
  58. Private Sub DeleteField(db As Database, ByVal strTableName As String, _
  59. ByVal strFieldName As String)
  60. Dim tdf As TableDef
  61. Const conItemNotFoundInThisCollection = 3265
  62. On Error GoTo XEH
  63. Set tdf = db.TableDefs(strTableName)
  64. tdf.Fields.Delete (strFieldName)
  65. db.TableDefs.Refresh
  66. XEH: If Err = conItemNotFoundInThisCollection Then Exit Sub
  67. End Sub
  68.  
  69. Private Sub DeleteIndex(db As Database, ByVal strTableName As String, ByVal _
  70. strIndexName As String)
  71. Dim tdf As TableDef
  72. Const conItemNotFoundInThisCollection = 3265
  73. On Error GoTo XEH
  74. Set tdf = db.TableDefs(strTableName)
  75. tdf.Indexes.Delete (strIndexName)
  76. db.TableDefs.Refresh
  77. XEH: If Err = conItemNotFoundInThisCollection Then Exit Sub
  78. End Sub
  79.  
  80. Private Sub DeleteTable(db As Database, ByVal strTableName As String)
  81. Const conItemNotFoundInThisCollection = 3265
  82. On Error GoTo XEH
  83. db.TableDefs.Delete (strTableName)
  84. db.TableDefs.Refresh
  85. XEH: If Err = conItemNotFoundInThisCollection Then Exit Sub
  86. End Sub
  87.  
  88. Private Sub CreateTable(db As Database, udtFieldProperties() As FieldProps)
  89. Dim tdf As TableDef, i As Long
  90. Set tdf = db.CreateTableDef(udtFieldProperties(1).TableName)
  91. For i = 1 To UBound(udtFieldProperties)
  92.   CreateField tdf, udtFieldProperties(i)
  93.   If udtFieldProperties(i).FieldIdx <> "None" Then
  94.     CreateIndex tdf, udtFieldProperties(i)
  95.   End If
  96. Next i
  97. db.TableDefs.Append tdf
  98. db.TableDefs.Refresh
  99. End Sub
  100.  
  101. Private Sub CreateField(ByVal tdf As TableDef, udtFieldProperties As FieldProps)
  102. Dim fld As Field
  103. With udtFieldProperties
  104.   Set fld = tdf.CreateField(.FieldName, .FieldType, .FieldSize)
  105.   If .FieldType = dbText Then
  106.     fld.AllowZeroLength = .FieldAllowZeroLength
  107.   End If
  108.   fld.Required = .FieldRequired
  109. End With
  110. tdf.Fields.Append fld
  111. tdf.Fields.Refresh
  112. End Sub
  113.  
  114. Private Sub CreateIndex(ByVal tdf As TableDef, udtFieldProperties As FieldProps)
  115. Dim idx As Index, idxfld As Field
  116. With udtFieldProperties
  117.   Set idx = tdf.CreateIndex("IDX_" & .FieldName)
  118.   Set idxfld = idx.CreateField(.FieldName)
  119.   idx.Fields.Append idxfld 
  120.   If .FieldIdx = "Primary" Then
  121.     idx.Primary = True
  122.     idx.Required = True
  123.   End If
  124.   If .FieldIdx = "Primary" Or .FieldIdx = "Unique" Then
  125.     idx.Unique = True
  126.   End If
  127.   tdf.Indexes.Append idx
  128.   tdf.Indexes.Refresh
  129. End With
  130. End Sub
  131.  
  132.  

PEB's Avatar
PEB PEB is offline
Expert
  
Join Date: Aug 2006
Location: Bulgaria
Posts: 1,380
#2: Jan 9 '07

re: Use of DAO In MS Access 2000 With a SQL Server 2000 DB

As I see u want to modify the backend database using the front end application!

I'm pretty sure that with ADO db can't be done! But sure u can do it with Pass trought SQL expressions - only u pass the respective SQL in SQL server directly to it! And it modifies the fields tables and structures!


Quote:

Originally Posted by Sam Shaw

I have been looking after an MS Access database, using table links to
access data in a back-end MDB database. We have recently micrated to a
SQL Server 2000 back-end atabase, once again accessing it though table links.

I am comfortable with the data side of things (whether through table
links or an adodb.recordset). What I am missing, however, is all the
useful things you could do to automatically maintain a back-end database
from a front-end database via the DAO 'Database' Object which exposes
the TableDefs Collection, the TableDef Object, the Fields Collection, the
Field, the Indexes Collection and the Index object.

Is there any way I can, from an Access 2000 front-end database, connect
to a SQL Server 2000 database as a 'database' to perform the tasks in the
following procedures.

Expand|Select|Wrap|Line Numbers
  1.  
  2. Private Sub SetBackEndDatabase(db As Database)
  3. Set db = _
  4. OpenDatabase("w:\backend\backend.mdb", False, False, ";PWD=" & "BackEndDatabasePassword")
  5. End Sub
  6.  
  7. Private Sub ModifyField(db, udtFieldProperties As FieldProps)
  8. Dim tdf As TableDef, fld As Field
  9. With udtFieldProperties
  10.   Set tdf = db.TableDefs(.TableName)
  11.   Set fld = tdf.Fields(.FieldName)
  12.   If .FieldType = dbText Then
  13.     fld.AllowZeroLength = .FieldAllowZeroLength
  14.   End If
  15.   fld.Required = .FieldRequired
  16. End With
  17. db.TableDefs.Refresh
  18. End Sub
  19.  
  20. Private Sub CreateIndexInExistingTable(db, udtFieldProperties As FieldProps)
  21. Dim tdf As TableDef
  22. Set tdf = db.TableDefs(udtFieldProperties.TableName)
  23. CreateIndex tdf, udtFieldProperties
  24. End Sub
  25.  
  26. Private Sub CreateFieldInExistingTable(db, udtFieldProperties As FieldProps)
  27. Dim tdf As TableDef
  28. Set tdf = db.TableDefs(udtFieldProperties.TableName)
  29. CreateField tdf, udtFieldProperties
  30. End Sub
  31.  
  32. Private Sub RenameField(db As Database, ByVal strTableName As String, ByVal _
  33. strOriginalFieldName As String, ByVal strNewFieldName As String)
  34. Dim tdf As TableDef, fld As Field
  35. Set tdf = db.TableDefs(strTableName)
  36. Set fld = tdf.Fields(strOriginalFieldName)
  37. fld.Name = strNewFieldName
  38. db.TableDefs.Refresh
  39. End Sub
  40.  
  41. Private Sub RenameIndex(db As Database, ByVal strTableName As String, ByVal _
  42. strOriginalIndexName As String, ByVal strNewIndexName As String)
  43. Dim tdf As TableDef, idx As Index
  44. Set tdf = db.TableDefs(strTableName)
  45. Set idx = tdf.Indexes(strOriginalIndexName)
  46. idx.Name = strNewIndexName
  47. db.TableDefs.Refresh
  48. End Sub
  49.  
  50. Private Sub RenameTable(db As Database, ByVal strOriginalTableName As String, _
  51. ByVal strNewTableName As String)
  52. Dim tdf As TableDef
  53. Set tdf = db.TableDefs(strOriginalTableName)
  54. tdf.Name = strNewTableName
  55. db.TableDefs.Refresh
  56. End Sub
  57.  
  58. Private Sub DeleteField(db As Database, ByVal strTableName As String, _
  59. ByVal strFieldName As String)
  60. Dim tdf As TableDef
  61. Const conItemNotFoundInThisCollection = 3265
  62. On Error GoTo XEH
  63. Set tdf = db.TableDefs(strTableName)
  64. tdf.Fields.Delete (strFieldName)
  65. db.TableDefs.Refresh
  66. XEH: If Err = conItemNotFoundInThisCollection Then Exit Sub
  67. End Sub
  68.  
  69. Private Sub DeleteIndex(db As Database, ByVal strTableName As String, ByVal _
  70. strIndexName As String)
  71. Dim tdf As TableDef
  72. Const conItemNotFoundInThisCollection = 3265
  73. On Error GoTo XEH
  74. Set tdf = db.TableDefs(strTableName)
  75. tdf.Indexes.Delete (strIndexName)
  76. db.TableDefs.Refresh
  77. XEH: If Err = conItemNotFoundInThisCollection Then Exit Sub
  78. End Sub
  79.  
  80. Private Sub DeleteTable(db As Database, ByVal strTableName As String)
  81. Const conItemNotFoundInThisCollection = 3265
  82. On Error GoTo XEH
  83. db.TableDefs.Delete (strTableName)
  84. db.TableDefs.Refresh
  85. XEH: If Err = conItemNotFoundInThisCollection Then Exit Sub
  86. End Sub
  87.  
  88. Private Sub CreateTable(db As Database, udtFieldProperties() As FieldProps)
  89. Dim tdf As TableDef, i As Long
  90. Set tdf = db.CreateTableDef(udtFieldProperties(1).TableName)
  91. For i = 1 To UBound(udtFieldProperties)
  92.   CreateField tdf, udtFieldProperties(i)
  93.   If udtFieldProperties(i).FieldIdx <> "None" Then
  94.     CreateIndex tdf, udtFieldProperties(i)
  95.   End If
  96. Next i
  97. db.TableDefs.Append tdf
  98. db.TableDefs.Refresh
  99. End Sub
  100.  
  101. Private Sub CreateField(ByVal tdf As TableDef, udtFieldProperties As FieldProps)
  102. Dim fld As Field
  103. With udtFieldProperties
  104.   Set fld = tdf.CreateField(.FieldName, .FieldType, .FieldSize)
  105.   If .FieldType = dbText Then
  106.     fld.AllowZeroLength = .FieldAllowZeroLength
  107.   End If
  108.   fld.Required = .FieldRequired
  109. End With
  110. tdf.Fields.Append fld
  111. tdf.Fields.Refresh
  112. End Sub
  113.  
  114. Private Sub CreateIndex(ByVal tdf As TableDef, udtFieldProperties As FieldProps)
  115. Dim idx As Index, idxfld As Field
  116. With udtFieldProperties
  117.   Set idx = tdf.CreateIndex("IDX_" & .FieldName)
  118.   Set idxfld = idx.CreateField(.FieldName)
  119.   idx.Fields.Append idxfld 
  120.   If .FieldIdx = "Primary" Then
  121.     idx.Primary = True
  122.     idx.Required = True
  123.   End If
  124.   If .FieldIdx = "Primary" Or .FieldIdx = "Unique" Then
  125.     idx.Unique = True
  126.   End If
  127.   tdf.Indexes.Append idx
  128.   tdf.Indexes.Refresh
  129. End With
  130. End Sub
  131.  
  132.  
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,747
#3: Jan 9 '07

re: Use of DAO In MS Access 2000 With a SQL Server 2000 DB

Sam,
If you have a question to ask - please give specifics.
The tasks you want done need to be described clearly in your question. Expecting the experts to dig through your code to save you the trouble is not how it should work.

PEB's answer probably gives you the information you need I suspect, however. You will need the expertise to handle the TSQL side of things obviously. We have a SQL Server forum where our experts will be happy to give further assistance if requested.
Good luck in your project.

MODERATOR.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,533 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved