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

Refreshing and linking SQL Tables to Access front end

P: n/a
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

Feb 15 '06 #1
Share this question for a faster answer!
Share on Google+

This discussion thread is closed

Replies have been disabled for this discussion.