Make local copy of backend table - how?

[Jim Devenish]

I wish to copy a table on a SQL Server backend to a table on an Access
(.mdb) front end in the simplest way.

I have the following to get the recordset but am seeking something
easier.

Dim theConnectionSt ring
theConnectionSt ring = "Driver=SQL Server;
Server=Server;D atabase=myData; Trusted_Connect ion=Yes"

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

Dim theSQLString
theSQLString = "Select * From backEndTable"

rec.Open theSQLString, theConnectionSt ring, adOpenStatic,
adLockOptimisti c

Do Until rec.EOF
' copy each record to the frontEndTable, field by field
rec.MoveNext
Loop

This will work but I want to be able to copy from the back end to the
front end in a simpler way. Either some SQL on the backend which can
copy directly to the frontend; but I do not know how to specify the
frontend table in a way that the backend understands.

Alternatively, having got the recordset, I would like to copy all of it
to the frontend table in a single statement without having to loop
through the records. Something like "copy the recordset 'rec' to
the table 'frontEndTable' "

Are either of these possible? If so, how?

[Allen Browne]

Link the SQL Server table.

Create a Make Table query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim Devenish" <in************ ***@foobox.comw rote in message
news:11******** *************@n 67g2000cwd.goog legroups.com...

>I wish to copy a table on a SQL Server backend to a table on an Access
(.mdb) front end in the simplest way.

I have the following to get the recordset but am seeking something
easier.

Dim theConnectionSt ring
theConnectionSt ring = "Driver=SQL Server;
Server=Server;D atabase=myData; Trusted_Connect ion=Yes"

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

Dim theSQLString
theSQLString = "Select * From backEndTable"

rec.Open theSQLString, theConnectionSt ring, adOpenStatic,
adLockOptimisti c

Do Until rec.EOF
' copy each record to the frontEndTable, field by field
rec.MoveNext
Loop

This will work but I want to be able to copy from the back end to the
front end in a simpler way. Either some SQL on the backend which can
copy directly to the frontend; but I do not know how to specify the
frontend table in a way that the backend understands.

Alternatively, having got the recordset, I would like to copy all of it
to the frontend table in a single statement without having to loop
through the records. Something like "copy the recordset 'rec' to
the table 'frontEndTable' "

Are either of these possible? If so, how?

[Keith Wilby]

"Jim Devenish" <in************ ***@foobox.comw rote in message
news:11******** *************@n 67g2000cwd.goog legroups.com...

>I wish to copy a table on a SQL Server backend to a table on an Access
(.mdb) front end in the simplest way.
Alternatively, having got the recordset, I would like to copy all of it
to the frontend table in a single statement without having to loop
through the records. Something like "copy the recordset 'rec' to
the table 'frontEndTable' "

Are either of these possible? If so, how?

How about a make-table query? Or am I missing the point?

Keith.
www.keithwilby.com

[Lyle Fairfield]

Jim Devenish wrote:

I wish to copy a table on a SQL Server backend to a table on an Access
(.mdb) front end in the simplest way.

Older ways include:

Create a sister ADP file connected to the SQL db. Then (from the MDB):
DoCmd.TransferD atabase acImport, "Microsoft Access", _
ADPFile, acTable, TABLE_NAME, TABLE_NAME, False
-
Create an ODBC link: Then
DoCmd.TransferD atabase acLink, "ODBC Database", _
"ODBC;DSN=DataS ource1;UID=User 2;PWD=www;LANGU AGE=us_english; " _
& "DATABASE=pubs" , acTable, "Authors", "dboAuthors "
-
Create a specific non-DSN odbc link to the table:
Sub adub()

Dim connect$
Dim tdf As DAO.TableDef
Dim db As DAO.Database

strConnect = "ODBC;DRIVER={S QL Server}" _
& ";SERVER=______ ____" _
& ";DATABASE=____ ______" _
& ";UID=_________ _" _
& ";PWD=_________ _"

Set db = CurrentDb()
Set tdf = db.CreateTableD ef("LocalFFDBAA ccounts")
tdf.SourceTable Name = "FFDBAAccou nts"

tdf.connect = connect

db.TableDefs.Ap pend tdf
db.TableDefs.Re fresh

End Sub

Then use the table or just copy it and then remove the link.

--
.... there may be newer ways; if there are we can hope someone will tell
us.

[Jim Devenish]

I was trying to do it without linking to the backend table. Since I
can access the backend table without a link, I was hoping to make a
copy as well.

But thanks anyway. I will try it with a link

Jim

Allen Browne wrote:

Link the SQL Server table.

Create a Make Table query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Jim Devenish" <in************ ***@foobox.comw rote in message
news:11******** *************@n 67g2000cwd.goog legroups.com...

I wish to copy a table on a SQL Server backend to a table on an Access
(.mdb) front end in the simplest way.

I have the following to get the recordset but am seeking something
easier.

Dim theConnectionSt ring
theConnectionSt ring = "Driver=SQL Server;
Server=Server;D atabase=myData; Trusted_Connect ion=Yes"

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

Dim theSQLString
theSQLString = "Select * From backEndTable"

rec.Open theSQLString, theConnectionSt ring, adOpenStatic,
adLockOptimisti c

Do Until rec.EOF
' copy each record to the frontEndTable, field by field
rec.MoveNext
Loop

This will work but I want to be able to copy from the back end to the
front end in a simpler way. Either some SQL on the backend which can
copy directly to the frontend; but I do not know how to specify the
frontend table in a way that the backend understands.

Alternatively, having got the recordset, I would like to copy all of it
to the frontend table in a single statement without having to loop
through the records. Something like "copy the recordset 'rec' to
the table 'frontEndTable' "

Are either of these possible? If so, how?