|
I have a routine that replicates a table with an existing table (used
as a template) in SQL Server. The desired new table name is passed to
the routine along with the template table name.
The code successfully creates the new table, but it doesn't appear
until I manually refresh the Tables list in SQL Server.
I need the vba code that will refresh the SQL Tables and then link the
newly created table back to the MS-Access front end. I have included
the routine so you can see my elegant code <grin>.
Thanks in advance for any help.
Public Sub DuplicateSQLTable(strNewTable As String, strTemplate As
String)
Dim cn As New ADODB.Connection
Dim rst As ADODB.Recordset
Dim blnExists As Boolean
'SQL_SERVER_NAME = The name of the SQL Server
'SQL_DATABASE_NAME = The name of the database
' Opening connection using Windows Authentication to connect to the SQL
Server
cn.Open "Provider=SQLOLEDB.1;Data Source=" & SQL_SERVER_NAME & "; & _
Initial Catalog=" & SQL_DATABASE_NAME & ";Integrated Security='SSPI'"
'Check to see if table already exists
Set rst = cn.OpenSchema(adSchemaTables)
blnExists = False
Do Until rst.EOF
If rst!TABLE_NAME = strNewTable Then
blnExists = True
Exit Do
End If
rst.MoveNext
Loop
rst.Close
set rst = Nothing
If blnExists Then 'Drop the existing table
cn.Execute "DROP TABLE " & strNewTable
End If
'Create the table
cn.Execute "Select * INTO " & strNewTable & " FROM " & strTemplate
'Now link the table back to Access
'This is where I need the code
cn.Close
Set cn = Nothing
End Sub
| 
