Local Tables

[Mark A. Sam]

I am new using SQL Server and ADP's. I set up a test project and linked to
an SQL Server Database. I then tried to import my local tables into the
project and saw that they get transported to the SQL Database. I then moved
the local tables into a new .mdb and created a dsn to that database, and
linked. They display as queries and have non-updatable recordsets. I need
them as local tables. Is there anything that I can do?

Thank you and God Bless,

Mark A. Sam

 

[J. Clay]

ADPs cannot hold tables. All data is on the SQL server. The best solution
is to use Temp Table in SQL that is accessible by individual sessions.

HTH,
J. Clay

 

[Mark A. Sam]

Thanks Clay,

It doesn't sound feasible, but thanks for the respone.

God Bless,

Mark

 

[Mark A. Sam]

Lyle,

" What about transferring data from the server to the local db? It's fairly
easy to use the Import-Export Wizard, or DTS from the Enterprise Manager,
to set this up. You can save the DTS "package" tp a local file, and you
can "call" that package from VBA code. "

That is what I want to do, but the ADP doesn't seem to have the capacity to
hold a local table. All the other things are too time consuming. This app
has hundreds of forms, hundreds of queires, hundreds of sql statements
within forms, many local tables, many table get created, and many local
tables which get appended.

I've decided that it isn't worth considering anything other than linking. I
no longer have patience to deal with the myriad of issues related to running
any microsoft program. Pretty soon the whole world is going to crash and it
will be becuase it didn't install the right patch.

God Bless,

Mark

 

[Lyle Fairfield]

Lyle,

" What about transferring data from the server to the local db? It's
fairly easy to use the Import-Export Wizard, or DTS from the Enterprise
Manager, to set this up. You can save the DTS "package" tp a local file,
and you can "call" that package from VBA code. "

That is what I want to do, but the ADP doesn't seem to have the capacity
to hold a local table. All the other things are too time consuming.
This app has hundreds of forms, hundreds of queires, hundreds of sql
statements within forms, many local tables, many table get created, and
many local tables which get appended.

I've decided that it isn't worth considering anything other than
linking. I no longer have patience to deal with the myriad of issues
related to running any microsoft program. Pretty soon the whole world
is going to crash and it will be becuase it didn't install the right
patch.

God Bless,

Mark

Well I can't agree that it's so much work;

Here's the code from the local mdb:

Public Function LocalTransactionTable() As ADODB.Recordset
Set LocalTransactionTable = New ADODB.Recordset
With LocalTransactionTable
.ActiveConnection = CodeProject.Connection
' NOTE !!!!!!!!!!!!!! CodeProject !!!!!!!!!!!!!!!
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockBatchOptimistic
.Open ("SELECT * FROM Table1")
End With
End Function

As I said, in the adp we set a reference to the mdb.

Here's the code from the adp:

Sub test2()
Dim l As ADODB.Recordset
Dim s As ADODB.Recordset
Set l = LocalTransactionTable()
Set s = LocalTransactionTable()
With s
.ActiveConnection = CurrentProject.Connection
.Resync
End With
With l
Do While Not .EOF
s.AbsolutePosition = .AbsolutePosition
s.Fields(1).Value = .Fields(1).Value
.MoveNext
Loop
End With
s.UpdateBatch
Set s = Nothing
Set l = Nothing
End Sub

What does it do? It updates Field(1) of the (SQL) server table with the
values in Field(1) of the (MDB) local table.

Time consuming? Yes, the code consumed ten minutes (to write).

Is it whole, tested, ready for critical work? Not yet! But would it be a
big deal to make it so? I don't think so.

I've never understood the need for local tables, so I'm not so concerned
about this. But local tables and ADPS are comapatible, of this I'm
reasonably confident.