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 theConnectionString
theConnectionString = "Driver=SQL Server;
Server=Server;Database=myData;Trusted_Connection=Y es"

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

Dim theSQLString
theSQLString = "Select * From backEndTable"

rec.Open theSQLString, theConnectionString, adOpenStatic,
adLockOptimistic

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.comwrote in message
news:11*********************@n67g2000cwd.googlegro ups.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 theConnectionString
theConnectionString = "Driver=SQL Server;
Server=Server;Database=myData;Trusted_Connection=Y es"

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

Dim theSQLString
theSQLString = "Select * From backEndTable"

rec.Open theSQLString, theConnectionString, adOpenStatic,
adLockOptimistic

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.comwrote in message
news:11*********************@n67g2000cwd.googlegro ups.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.TransferDatabase acImport, "Microsoft Access", _
ADPFile, acTable, TABLE_NAME, TABLE_NAME, False
-
Create an ODBC link: Then
DoCmd.TransferDatabase acLink, "ODBC Database", _
"ODBC;DSN=DataSource1;UID=User2;PWD=www;LANGUAGE=u s_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={SQL Server}" _
& ";SERVER=__________" _
& ";DATABASE=__________" _
& ";UID=__________" _
& ";PWD=__________"

Set db = CurrentDb()
Set tdf = db.CreateTableDef("LocalFFDBAAccounts")
tdf.SourceTableName = "FFDBAAccounts"

tdf.connect = connect

db.TableDefs.Append tdf
db.TableDefs.Refresh

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.comwrote in message
news:11*********************@n67g2000cwd.googlegro ups.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 theConnectionString
theConnectionString = "Driver=SQL Server;
Server=Server;Database=myData;Trusted_Connection=Y es"

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

Dim theSQLString
theSQLString = "Select * From backEndTable"

rec.Open theSQLString, theConnectionString, adOpenStatic,
adLockOptimistic

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?