Import DNSless tables

[mcgrcoAgain]

Hi There,

I have to manually Import some sql server tables that are hugh and i want to atomate the process. I have some code for attaching DNSless tables but really i need to import them. I tried to run make tables on the attached tables but the performance was dire. Importing the tables proved much quicker. The current code I have is :

Any help is appreciated.

Function AttachDSNLessTable(stRemoteTableName As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
Dim stLocalTableName As String
Dim stServer As String
Dim stDatabase As String

stLocalTableName = "Tablename"
stServer = "Servername"
stDatabase = "testerArchive"




For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next

stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"

Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function

AttachDSNLessTable_Err:

AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description

End Function

 

[KenHigg]

Did you try append?


ken

 

[WayneRyan]

M,

Why not just link table --> tblSQLServer

DoCmd.RunSQL "Select * Into tblAccessDB From tblSqlServer

Then drop the link to tblSQL Server (or just not reference it).

I don't import into Access from SQL Server, does it preserve indexes?
You might have to add them later.

Or change the "Select * Into ..." to a "Delete From" and "Insert Into ..." combination. Then you can preserve the indexes.

Wayne

 

[mcgrcoAgain]

I ran an append on the linked table and then dropped the table as suggested. It works well.

For reference I think that Access preserves indexes... when importing from sql serever, although I think theres a limit to the number of indexes access can cope with.

 

[KenHigg]

Glad you found a solution -

ken