connecting Access front-end to SQLServer back-end via code

[Jim Devenish]

I am in the early stages of converting a back-end .mdb file to
SqlServer - my first attempt! I have used Data Transformation Services
to copy all the tables to SqlServer into a database named JobSystem.
When using Enterprise Manager, I can see all the data. So far, so
good!!

From within the front-end I want to link my tables and have tried the

following code:

For Each tdf In CurrentDb.TableDefs
If (tdf.Attributes = dbAttachedODBC) Or (tdf.Attributes =
dbAttachedTable) Then
Dim aTable As DAO.TableDef
Dim tableName
tableName = tdf.Name
Set aTable = CurrentDb.TableDefs(tableName)
If Err.Number = 0 Then ' found existing tabledef
'On Error GoTo nextTable
CurrentDb.TableDefs.Delete tableName
CurrentDb.TableDefs.Refresh
Else
Err.Clear
End If
Set aTable = CurrentDb.CreateTableDef(tableName)
aTable.Connect = "ODBC;DataBase=JobSystem; DRIVER=SQLServer; " _
& "SERVER=(local);APP=Microsoft Data Access Components; " _
& "Trusted_Connection=Yes;"
aTable.SourceTableName = tableName
CurrentDb.TableDefs.Append aTable
nextTable:
End If
Next tdf

All the tables link correctly but unfortunately they are read-only !!
I assume that there must be something wrong with the connection string.
But what, I do not know.

If, instead of using the code, I use 'Get external data|Link tables
....' from the File menu everything works well and the tables are
updateable. However all the links are now given names such as
dbo_MyTable instead of simply MyTable; and during the linking process I
am asked' for each table' to select a field in order 'To ensure
data integrity and to update records, you must choose a field that
uniquely identifies each record'

When I have linked the tables in this way, the connection string (shown
in the properites of the table) is the same as when I use the above
code (except that the table to which it is linked is dbo.MyTable)

How can I make the link with code to get tables that are updateable?

[Tom van Stiphout]

On 12 Mar 2006 09:53:37 -0800, "Jim Devenish"
<in***************@foobox.com> wrote:

Attached tables are readonly if the tables don't have a primary key.
Adding a primary key to each table is a good design practice anyway.

-Tom.
<clip>

All the tables link correctly but unfortunately they are read-only !!
I assume that there must be something wrong with the connection string.
But what, I do not know.

<clip>

[Devonish]

Tom

Many thanks for the help. How did I miss that? Should have read the
small
print.

My Access tables do have primary keys and assumed that DTS would copy
them
into SQLServer, even though I did not appreciate their full
significance.

I now realise that I was using the DTS wizard but am now exploring the
full DTS
package. I want to be able to create SQLServer tables with primary keys
(as in
the Access tables) without having to go the design of every one of them
to re-
introduce them.

Thanks again
Jim Devenish

*** Sent via Developersdex http://www.developersdex.com ***