Access to SQL Server - Upsizing - dbo

[Annette Massie]

Let me try this again. I received an error when I tried to post this.
Pardon me if it appears twice.

I have an Access 2000 database that I would like to move the tables to
SQL Server 2000.

I have tried the upsizing tool and moved the tables over. Now will I
create a front end in Access and link to the ss tables? If so, I see
the ss tables are prefixed with dbo_. I already have the queries in
Access to refer to the table names that do not begin with dbo?

Please point me in the right direction of how to proceed.

Thanks!

[F. Michael Miller]

Change the queries to the new table names.
"Annette Massie" <an******@co.saint-croix.wi.us> wrote in message
news:c6**************************@posting.google.c om...

Let me try this again. I received an error when I tried to post this.
Pardon me if it appears twice.

I have an Access 2000 database that I would like to move the tables to
SQL Server 2000.

I have tried the upsizing tool and moved the tables over. Now will I
create a front end in Access and link to the ss tables? If so, I see
the ss tables are prefixed with dbo_. I already have the queries in
Access to refer to the table names that do not begin with dbo?

Please point me in the right direction of how to proceed.

Thanks!

[Tony Toews]

an******@co.saint-croix.wi.us (Annette Massie) wrote:

Let me try this again. I received an error when I tried to post this.
Pardon me if it appears twice.

I have an Access 2000 database that I would like to move the tables to
SQL Server 2000.

I have tried the upsizing tool and moved the tables over. Now will I
create a front end in Access and link to the ss tables? If so, I see
the ss tables are prefixed with dbo_. I already have the queries in
Access to refer to the table names that do not begin with dbo?

Use some code to relink the tables. But when you create the link drop the dbo_ from
the local table name. I misremember the exact details of the lines of code but that
should work.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Tony Toews]

"F. Michael Miller" <fm***@netzero.net> wrote:

Change the queries to the new table names.

That's a lot of work. Then there's testing.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

[Wayne Gillespie]

On 12 May 2004 07:40:41 -0700, an******@co.saint-croix.wi.us (Annette Massie) wrote:

Let me try this again. I received an error when I tried to post this.
Pardon me if it appears twice.

I have an Access 2000 database that I would like to move the tables to
SQL Server 2000.

I have tried the upsizing tool and moved the tables over. Now will I
create a front end in Access and link to the ss tables? If so, I see
the ss tables are prefixed with dbo_. I already have the queries in
Access to refer to the table names that do not begin with dbo?

Please point me in the right direction of how to proceed.

Thanks!

Sub RenameLinks()
Dim Bfdb As DAO.Database
Dim tdf As TableDef

On Error GoTo HandleIt

DoCmd.Hourglass True
Set Bfdb = CurrentDb()

For Each tdf In Bfdb.TableDefs
If Left(tdf.Name, 4) = "dbo_" Then
tdf.Name = Right(tdf.Name, Len(tdf.Name) - 4)
End If
Next tdf

OutHere:
If Not (tdf Is Nothing) Then Set tdf = Nothing
If Not (Bfdb Is Nothing) Then Set Bfdb = Nothing
DoCmd.Hourglass False
On Error GoTo 0
Exit Sub

HandleIt:
Select Case Err.Number
Case 0
Resume Next
Case Else
Beep
MsgBox Err & "; " & Err.Description, vbCritical + vbOKOnly, "Error"
Resume OutHere
End Select

End Sub

Wayne Gillespie
Gosford NSW Australia