Hum, after reading back your original post, I might have been thrown off of
your real problem with the following statement:
» The conversion went well but I am having trouble finding how to link
» the Front End Application to the converted backend.
» When I click on the Linked Table manager it appears the only thing it will
» link me to is another Access *.mdb/*.accdb database and I cannot find
another
» option to link to the SQLServer backend.
When you convert a split database, once your tables are on the sql-server,
you don't need the old backend database anymore and all you have to do is to
relink the links on the frontend to the new backend database; ie. the tables
on the SQL-Server. Also, you don't need to make this last step is you are
using DSNs and you have changed the DSNs to point toward the SQL-Server
instead of pointing toward the old MDB backend database.
As you don't need the old MDB backend database anymore, what you get in it -
ie., the two sets of tables - is not relevant at all.
All you have to do is to use the Linked Table Manager to relink your ODBC
linked tables in the frontend toward the tables on the SQL-Server; keeping
the same names in the frontend as they were in the old frontend.
You could also re-create these linked tables using the "More" option that
you'll find under the "External Data" tab like I said in a previous post.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by RobmanSylvan - thanks for your persistance!
I discovered what you wrote late yesterday but perhaps I am going about this
wrong in that I am converting the back end database and then trying to link
the Front End to it - which doesn't seem to work since I end up with 2 sets
of linked tables - the original one and the new ODBC linked tables which have
dbo.tablename as their names. Perhaps I should be converting the front end
application which has the linked tables and the conversion utility will take
care of both?
I will try this to see what happens and let you know!
Thank you for all your help!
Post by unknownI've just took a look at a copy of Office 2007 and from its menu, you must
click on: "External Data" | More | ODBC Database | "Link to the data source
by creating a linked table" | [OK ] | "Machine Data Source" | [New...] |
"System Data Source (...)" | [Next >] | "SQL Server" in order to create a
new ODBC linked table.
Notice that in the last step, you could also use "SQL Server Native Driver"
instead of "SQL Server" as the data source if you have a version of
SQL-Server greater or equal to SQL-Server 2005.
The "Linked Table Manager" is for managing already created ODBC linked
tables and cannot be used to create a new ODBC linked table (however, it can
be used to change an already created ODBC linked table).
Also, if you have already upsized the database to Sql-Server, then you
shouldn't have to do anything because Access is supposed to leave you with a
MDB or ACCDB database file with all the talbes already linked to the
database, unless you have chosen to create an ADP project file or that you
have chosen not to export all the tables. (I don't know what option you
have chosen with upsizing wizard.).
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by RobmanWe run Norton Antivirus Corporate Edition and can find no setting where it
disables Office Add ins so I don't think that is the problem.
I have a Front End and a Back End Access2007 application. The front
end
is
simply that - the forms, queries, reports etc - the back end is the actual
tables (the data). What I want to do is to move the back end into SQLServer
and then have the front end link to the tables on the SQLServer (the
new
back
end).
I cannot find any option under the Linked Table manager to do so, is it
possible that there was an installation choice on the Access that I didn't
choose? If not then I am at a loss to understand how to make this work?
Thanks again for your reply!
Post by unknownI cannot find a way to tell Access that I want to link through an ODBC
source.
The most likely explanation to that is that this is the result of your
antivirus. For example, if you have an older version of Norton Anti-Virus
(NAV), look for an option called the "Office Plug-in". Desactivate this
option (temporarily is necessary) and you will be able to see the ODBC
sources option again in Access. For other, more recent versions of
NAV
or
for other antiviruses, the name of the option might be something else than
the "Office Plug-In".
Also, I'm not sure to fully understand your problem. The Linked Table
Manager is used to refresh or the change the connection of existing ODBC
linked tables; not to create new ones. To create a new ODBC linked table,
you must use the Import function of Access. However, I don't have an access
to a running copy of Access 2007 at this moment, so I cannot tell you for
sure.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Thanks for your answer but the problem is not that I cannot create
the
ODBC
connection, the problem is that when I click on the Linked Table
manager
in
Access 2007 after selecting all the tables and clicking the "prompt
for
new
location" button the only option I have is to choose an Access
database
such
as *.mdb or *.accdb. I cannot find a way to tell Access that I want
to
link
through an ODBC source.
Post by unknownYou have said it yourself: you have a collection of ODBC linked
tables
to
SQL-Server 2005, so you have nothing more to do; excerpt maybe
tweak
your
VBA code here and there for some incompatibilities..
If you want to create new linked tables with the Linked Table
Manager,
you
must first choose the ODBC Databases option - the last one if I remember
correctly; after that you can choose the SQL-Server ODBC driver
from
the
list of available drivers. You cannot choose to link to a
SQL-Server
table
without first selecting the ODBC Databases option.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)
Post by RobmanI have done a test of converting an Access 2007 back end database
(the
data
of an Access application) into SQLServer 2005 through the SQL Upsizing
wizard. The conversion went well but I am having trouble finding how
to
link
the Front End Application to the converted backend.
When I click on the Linked Table manager it appears the only thing
it
will
link me to is another Access *.mdb/*.accdb database and I cannot
find
another
option to link to the SQLServer backend.
I note that after conversion the original back end database now has
2
sets
of tables and the original tables are now labeled table_local and a
new
set
of linked tables have been created with the original names and
these
tables
are linked through a ODBC connection to the SQL2005 server.
Is it possible that what I have to do is link to the original
back
end
database again through the Linked Table manager and this will
establish
the
link to the SQL2005 database tables? I cannot find any
documentation
that
clarifies this for me in any way so if you anyone can help it
would
be
appreciated!
Thanks