This is my problem:

I have a database installed on SQL Server. I wish to be able to export
this database to a Access .mdb database.

My problem is that autonumber fields are not easily replicated.

Example:
A conference can have a number of participants

tblConference {id, confname}
1----n
tblConferenceParticipant{id, ConferenceID, ParticipantID}
n---1
tblParticipant{id, ParticipantName}

Data example:

1, conf1 1, 1, 5 5, Paul
2, conf2 2, 1, 7 7, Kim
3, 2, 1 11, John

My problem is not getting the data transferred to the access database. My
problem is that I need the database to be functional, eg. if a user
decides to use an Access copy of the database, it will produce new ids as
new participants are added etc.

The "gaps" (eg between 5 & 7 in tblParticipant) in the original SQL
Server database need to be copied as well. If they aren't, even if the
autonumber function works perfectly, the above will break: Since the ids
in tblParticipant would be 1,2 and 3 the relations in
tblConferenceParticipant would be "broken".

I have tried exporting the data to Access first, but I cannot bring
Access to make a field autonumber after puttning data into the table.

The fallback solution right now is to create a VBA script to run from
Access, importing the relevant tables from SQL Server.

Ideas would be greatly appreciated.

Sincerely
Poul Ingwersen

DTS doesnt seem to support this so I solved the problem by importing the
data from SQL Server to Access.

Why this has to be so troublesome is beyond me.

Make a new module in Access.
Paste this code:
(I use an ODBC DSN to get the data from SQLServer - SQLDatabaseLink in the
second part)

Option Compare Database



Public Sub CopyData()



Dim conn As ADODB.Connection

Dim cmd As ADODB.Command

Set cmd = New ADODB.Command

Set conn = New ADODB.Connection



Dim pass As String

Dim user As String



user = InputBox("Brugernavn")

pass = InputBox("Password")



conn.ConnectionString = "Provider=sqloledb;Data Source=<sql server
name>;Initial Catalog=<databasename>;User Id=" & user & ";Password=" & pass
& ";"

conn.Open



cmd.ActiveConnection = conn

cmd.CommandText = "SELECT TABLE_NAME " & _

"FROM INFORMATION_SCHEMA.Tables " & _

"WHERE (TABLE_TYPE = 'BASE TABLE') AND (TABLE_NAME <>
'dtproperties')"

Dim rs As ADODB.Recordset

Set rs = cmd.Execute







If Not (rs.EOF = True) And Not (rs.BOF = True) Then

Do While Not rs.EOF

tblName = rs.Fields(0).Value

DoCmd.TransferDatabase acImport, "ODBC Database", _

"ODBC;DSN=SQLDatabaseLink;UID= & user &;PWD= & pass &;" _

& "DATABASE=<databasename>", acTable, tblName, tblName, , True

rs.MoveNext

Loop

End If



'Rename tables

Dim cat As ADOX.Catalog

Set cat = New ADOX.Catalog

cat.ActiveConnection = CurrentProject.Connection

For Each Table In cat.Tables

If Left(Table.Name, 4) = "dbo_" Then

Table.Name = Right(Table.Name, Len(Table.Name) - 4)

End If

Next



End Sub
------------------------------------------------

Hopefully this will help someone else.

Poul



"Poul Ingwersen" <no-email (AT) mail (DOT) dk> wrote