How to create a table from SQL Server query result

[Vinod]

Hi All,

Please help me on how to create a new table in MS Access 2003 using a
select query which retrives data from external database (SQL Server 2005).

My present code looks as follows:
sub CopyToNewTable()
Dim qdfPass As DAO.QueryDef
Dim strSqlQry As String, strConn As String

strConn="ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-4B2723\SQLEXPRESS;DATABASE=emp;Trusted_Connection"
strSqlQry = "Select * Into tblTemp From emp In '" & strConn & "'"

currentdb.execute(strSqlQry);
end sub

After ran the above procedure 'tblTemp' table is created in SQL Server 2005.

But as per my requirement it needs to be created 'tblTemp' table in MS
Access DB where the sub procedure is running.

Please help me out by sharing your thoughts and ideas on resolving above
said issue in creating new table in local database.

Advanced Thanks
~Vins

 

[Douglas J. Steele]

Create (and save) a query that retrieves the data from SQL Server.

Use that saved query as the basis for the Make Table query.

 

[Stefan Hoffmann]

hi Vinod,

My present code looks as follows:
sub CopyToNewTable()
Dim qdfPass As DAO.QueryDef
Dim strSqlQry As String, strConn As String

strConn="ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-4B2723\SQLEXPRESS;DATABASE=emp;Trusted_Connection"
strSqlQry = "Select * Into tblTemp From emp In '" & strConn & "'"

currentdb.execute(strSqlQry);
end sub

After ran the above procedure 'tblTemp' table is created in SQL Server 2005.

But as per my requirement it needs to be created 'tblTemp' table in MS
Access DB where the sub procedure is running.

Please help me out by sharing your thoughts and ideas on resolving above
said issue in creating new table in local database.

Using a passthrough query in this way is not possible, as the SQL is
executed always on the SQL Server side. But you can use it like that:

Dim qd As DAO.QueryDef

Set qd = CurrentDb.CreateQeuryDef("pt", "SELECT * FROM emp")
qd.Connect = "ODBC;" & _
"DRIVER={SQL Server};" & _
"SERVER=PERSONAL-4B2723\SQLEXPRESS;" & _
"DATABASE=emp;Trusted_Connection=True"

CurrrenDb.QueryDefs.Refresh
CurrentDb.Execute "SELET * INTO localTable FROM pt", dbFailOnError

btw, why don't you use linked tables?


mfG
--> stefan <--

 

[Vinod]

Thanks Stefan for your immediate response.

The logic with the code you've provided is working fine as per my requirement.

Once again thank you very much in resloving my issue.

Regards
~Vins