Upsize to SQL

[apex77]

My Access database uses a front end (containing forms, queries and macros),
and a bcak end (tables). I have recently upsized the back end to SOL. Now my
queries, macros, forms do not work because they cannot locate the new tables.
Is there a tool that will allow me to relink the queries, macros, forms
without re-writing the over 200 queries that are in my database front end?

 

[Bob Barrows [MVP]]

My Access database uses a front end (containing forms, queries and
macros), and a bcak end (tables). I have recently upsized the back
end to SOL. Now my queries, macros, forms do not work because they
cannot locate the new tables. Is there a tool that will allow me to
relink the queries, macros, forms without re-writing the over 200
queries that are in my database front end?

Why not restore the original table names to the linked tables?

 

[Dale Fye]

I assume that the problem is that the linked SQL Server tables names all now
start with dbo_. If so, you could write some code to modify the Name
property of each of the tabledefs for the linked tables.

USING A COPY OF YOUR DATABASE

Private Sub RenameTables

Dim tdf as dao.TableDef
For each tdf in currentdb.tabledefs
if instr(tdf.name, "dbo_") Then
tdf.name = Mid(tdf.name, 5)
endif
next

End sub

This should change the local name of the table back to its original name,
while continuing to point to the table on the SQL Server. Once you have
renamed the tables, you should not have to do anything with your forms,
queries, or reports.

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.