Create Table in Access from SQL Server 2

[DanKay1]

I am trying to query information from the SQL server and when I am running Make Table Query and it creates it on SQL server instead of in Access.

Dim myConnection As ADODB.Connection
Set myConnection = New ADODB.Connection

Dim rstSource As ADODB.Recordset
Set rstSource = New ADODB.Recordset

myConnection.ConnectionString = "Provider=MSDASQL.1;" & _
"Persist Security Info=False;" & _
"User ID=;" & _
"Pwd=;Data Source=TAI_MONY"
myConnection.Open

rstSource.Open "SELECT REINSPM.* INTO TEST FROM REINSPM WHERE (((REINSPM.PM_PLAN)='3314'))", myConnection

'MsgBox (rstSource.Fields("PM_PLAN"))


' Close the connection.
myConnection.Close

 

[PHV]

Have you tried a maketable query based on a linked table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DanKay1]

SELECT REINSPM.* INTO TEST..."
I dont know does it tell the destination of where the Table should be made? or there is a Different way I can refer to make in access?

 

[cmmrfrds]

Are you just building some test data for the Access App, or is this part of the process for an application to be used by clients. There are multiple options on how to do this depending on the final product. The simple way but not the most efficient (good for small number of records) is to Link the sql server tables through Linked Table Manager as suggested by PHV.

 

[DanKay1]

I dont want people to see where the data is coming from and sometimes i will need to send database to external clients the link wont work then.

 

[cmmrfrds]

I am not clear on how the data will be sent to external clients. Is this a 2 step process? One app moves the data from sql server to an Access mdb and then the Access mdb is sent to the client?

 

[PHV]

Link an access table to REINSPM
Create a maketable query based on this linked table
Execute the maketable query
Remove the linked table.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[DanKay1]

Is it possible to do through ADO I dont want to do it manually since i have to link several tables.

 

[cmmrfrds]

You can do it through ADO. One way is to send a query to sql server that uses the OPENROWSET function. You need to pass the location of your Access mdb to sql server. I can give you an example that you can modify to your requirements.

This example to points to the northwind mdb that is loaded on the sql server server. Change to use a UNC to locate the access mdb on your PC. If you have northwind loaded on the sql server this example should run for you.


Public Function rowset()

Dim cn As New ADODB.Connection, sqlString As String
Dim rs As New ADODB.Recordset, connString As String
connString = "provider=SQLOLEDB.1;" & _
"User ID=sa;Initial Catalog=northwind;" & _
"Data Source=bigtuna;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString

sqlString = "SELECT c.*, o.* "
sqlString = sqlString & "FROM Northwind.dbo.Customers AS c INNER JOIN "
'--- point to the location and database name of the Access MDB.
sqlString = sqlString & "OPENROWSET('Microsoft.Jet.OLEDB.4.0', "
sqlString = sqlString & "'c:\program files\microsoft office\office\Samples\northwind.mdb';'admin';, Orders) "
sqlString = sqlString & "AS o ON c.CustomerID = o.CustomerID "

rs.Open sqlString, cn, adOpenForwardOnly, adLockReadOnly

If Not (rs.EOF = True) Then
Debug.Print "field name = "; rs.Fields(0).Name
Debug.Print "field value = "; rs.Fields(0).Value
End If
End Function

 

[VBslammer]

You can create a pass-through query that retrieves the data from SQL Server, then use it in an action query to create the local copy like this:

  Dim qdf As QueryDef
  Dim strConn As String
  
  [green]'create connection string[/green]
  strConn = "ODBC;Driver={SQL Server}; SERVER=TheServer; DATABASE=Northwind; UID=sa; PWD=myPwd;"
  
  [green]'create pass-through query using ODBC connection[/green]
  Set qdf = CurrentDb().CreateQueryDef("tempPassThrough", "SELECT * FROM dbo.Customers")
  qdf.Connect = strConn
  qdf.Close

  [green]'note pass-through query within SQL[/green]
  DoCmd.RunSQL "SELECT dbo.Customers.* INTO [localTable] FROM [blue]tempPassThrough[/blue]"  
  
  [green]'remove temp query[/green]
  DoCmd.DeleteObject acQuery, "tempPassThrough"

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[cmmrfrds]

Here is another way to do it with ADO by opening 2 ADO connections. This won't be as efficient as the first method since both recordset come back to the desktop, while the first method send an SQL string to sql server and only the data to load the Access table comes back.

Function ExternalTables2()
Dim cn As New ADODB.Connection, cn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset, rs2 As New ADODB.Recordset
Dim connString As String, connString2 As String
Dim sql1 As String, sql2 As String

connString = "provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\AEmptyDir\employee.mdb;" & _
"Persist Security Info=False"
cn.ConnectionString = connString
cn.Open connString
connString2 = "Provider=SQLOLEDB.1;Persist Security Info=False;" & _
"User ID=sa;Initial Catalog=Northwind;Data Source=localhost"
cn.ConnectionString = connString2
cn.Open connString2

sql1 = "select * from cn...employees as A inner join cn2...employees as B" & _
"ON A.employeeID = B.employeeID"
'sql1 = "Select * from categories"

rs.Open sql1, cn, adOpenStatic, adLockOptimistic
Debug.Print "rs = "; rs(0); " "; rs(1)

End Function

Another option that makes it easy from your side but requires the DBA to maintain a connection to your database. This is similar to the openrowset but the connection to the access mdb is done on sql server. It is called a linked server in sql server terminology. This way you can treat the Access mdb table just like any other sql server table. This is easy enough for the DBA to setup but they may not want to maintain since you need to keep the mdb in a fixed location or the DBA will need to redo.