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?