By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,037 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,037 IT Pros & Developers. It's quick & easy.

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

P: 1
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.  
Jan 9 '07 #1
Share this Question
Share on Google+
2 Replies


PEB
Expert 100+
P: 1,418
PEB
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!


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.  
Jan 9 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
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.
Jan 9 '07 #3

Post your reply

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