Upsizing to SQL Server

[wineguy via AccessMonster.com]

Access 2007, SQL Server 2000, XP.

New to access... built small app in Access... then split to FE/BE... works
fine.... next step was to use upsizing wizard to SQL Server... appears to be
successful... (can see tables in SQL Server, etc...) next step was to go to
Access front-end and (1) delete Access backend tables located in backend
accdb... and (2) to re-link to tables now in SQL Server...

also appears to be successful...

EXCEPT all the tables have "dbo_" preface to every table name...
(dbo_tablename) which means that all the table references in all queries are
now invalid... did I do something wrong in the process?

Thanks in advance,

glynn

 

[Sylvain Lafontaine]

Rename the tables by removing the dbo_ and your FE/BE will be OK. Next step
will be to add the dbSeeChanges option when opening a recordset with the
OpenRecordset method but Access will tell you where when you'll try to run
the VBA code.

Also, make sure that the upsizing process has added a primary key to each
SQL-Server tables; as this is often forgottent by the upsizing wizard. If
not, add it and refresh or relink the tables.

When linking to a View on SQL-Server, you must create a local virtual index
on the associated linked table if you want it to be updatable; see:

http://support.microsoft.com/kb/q209123/

Finally, as you are using it, AccessMonster make a pretty good job of hiding
that this newsgroup is about Access Data Projects (ADP) and SQL-Server and
has nothing to do with MDB (or ACCDB) ODBC Linked tables.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[Paul Shapiro]

Here's code that will remove the dbo_ table name prefix when run in the FE
db:
'Loop through all tables, checking names of those with Connect strings
For Each myTable In CurrentDb.TableDefs
If myTable.Connect <> "" Then
strTableName = myTable.Name
If Left(strTableName, 4) = "dbo_" Then
myTable.Name = Mid$(strTableName, 5)
End If
End If
Next myTable

You didn't do anything wrong. That's how Access interprets SQL Server's
schema.TableName id, which is commonly dbo.TableName. dbo is the
abbreviation for "DataBase Owner" and dates back to when SQL Server had the
nonstandard id of owner.TableName and the default object owner was dbo.

Rename the tables by removing the dbo_ and your FE/BE will be OK. Next
step will be to add the dbSeeChanges option when opening a recordset with
the OpenRecordset method but Access will tell you where when you'll try to
run the VBA code.

Also, make sure that the upsizing process has added a primary key to each
SQL-Server tables; as this is often forgottent by the upsizing wizard. If
not, add it and refresh or relink the tables.

When linking to a View on SQL-Server, you must create a local virtual
index on the associated linked table if you want it to be updatable; see:

http://support.microsoft.com/kb/q209123/

Finally, as you are using it, AccessMonster make a pretty good job of
hiding that this newsgroup is about Access Data Projects (ADP) and
SQL-Server and has nothing to do with MDB (or ACCDB) ODBC Linked tables.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

 

[wineguy via AccessMonster.com]

Hi Sylvain,

Thank you for your time and feedback.

Could you please point me to the appropriate AccessMonster thread to address
SQL Server and accdb questions?

Rename the tables by removing the dbo_ and your FE/BE will be OK. Next step
will be to add the dbSeeChanges option when opening a recordset with the
OpenRecordset method but Access will tell you where when you'll try to run
the VBA code.

Also, make sure that the upsizing process has added a primary key to each
SQL-Server tables; as this is often forgottent by the upsizing wizard. If
not, add it and refresh or relink the tables.

When linking to a View on SQL-Server, you must create a local virtual index
on the associated linked table if you want it to be updatable; see:

http://support.microsoft.com/kb/q209123/

Finally, as you are using it, AccessMonster make a pretty good job of hiding
that this newsgroup is about Access Data Projects (ADP) and SQL-Server and
has nothing to do with MDB (or ACCDB) ODBC Linked tables.

Access 2007, SQL Server 2000, XP.

[quoted text clipped - 16 lines]

glynn

 

[Sylvain Lafontaine]

Cannot tell, I don't use AccessMonster myself. However, I suppose that by
looking at some of the previous posts, you can get a good feel about the
subject of each newsgroup.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)

Hi Sylvain,

Thank you for your time and feedback.

Could you please point me to the appropriate AccessMonster thread to
address
SQL Server and accdb questions?

Rename the tables by removing the dbo_ and your FE/BE will be OK. Next
step
will be to add the dbSeeChanges option when opening a recordset with the
OpenRecordset method but Access will tell you where when you'll try to run
the VBA code.

Also, make sure that the upsizing process has added a primary key to each
SQL-Server tables; as this is often forgottent by the upsizing wizard. If
not, add it and refresh or relink the tables.

When linking to a View on SQL-Server, you must create a local virtual
index
on the associated linked table if you want it to be updatable; see:

http://support.microsoft.com/kb/q209123/

Finally, as you are using it, AccessMonster make a pretty good job of
hiding
that this newsgroup is about Access Data Projects (ADP) and SQL-Server and
has nothing to do with MDB (or ACCDB) ODBC Linked tables.

Access 2007, SQL Server 2000, XP.

[quoted text clipped - 16 lines]

glynn

 

[AccessVandal via AccessMonster.com]

eh... aren't you asking about ADP/SQL Server? I think you're in the right
forum. If you want SQL Server, look at the left frame of your Browser below
call Related Topices - SQL Server.

Hi Sylvain,

Thank you for your time and feedback.

Could you please point me to the appropriate AccessMonster thread to address
SQL Server and accdb questions?

Rename the tables by removing the dbo_ and your FE/BE will be OK. Next step
will be to add the dbSeeChanges option when opening a recordset with the

[quoted text clipped - 19 lines]

 

[Sylvain Lafontaine]

eh... aren't you asking about ADP/SQL Server? I think you're in the right
forum. If you want SQL Server, look at the left frame of your Browser
below
call Related Topices - SQL Server.

No, he's talking about ACCDB and ODBC Linked Tables, with no ADP in sight.

Hi Sylvain,

Thank you for your time and feedback.

Could you please point me to the appropriate AccessMonster thread to
address
SQL Server and accdb questions?

Rename the tables by removing the dbo_ and your FE/BE will be OK. Next
step
will be to add the dbSeeChanges option when opening a recordset with the

[quoted text clipped - 19 lines]

glynn

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)