How does this file know where to connect

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have 2 Access mdb files. (File A and B)

One contains all forms that edit the data in File B. It contains forms
only.(File A)

Another contains the actual data and tables. (File B)

However, I didn't created these files. Now we are trying to move all the
data and tables into the SQL server. So I need to update File A to make it
connect to the database in SQL server instead to File B.

However, I can't found out where in this file it specify how it connect to
database.

I try to view all the codes in Visual Basic view, all the Access Class
Objects and even the Modules. But no where in those codes secify which
database it connects.

So where else can this file A know where to connect to?
 
Open file A and look in the Tools menu under Database Utilities. You should
find "Linked Table Manager". This will show you the location of tables in
File B, and will allow you to change the location of those linked tables.

"Linked Table Manager" is an optional component at the time of Access
installation - if it's not listed in your tools menu you can add this from
the Access or Microsoft Office installation CD.

Robin
 
I should have added that linked tables are created in the first place by
selecting "Get external data" from the File menu, and then selecting "Link
tables"

R
 
Thanks for the answer. But the problem is, this file A, when I open it, the
menu bar is very limited. And it only shows the first form. This form, when
you click the buttons, will link to other forms that add/edit data.

There is no "Tools", and from "File", only has "Close". And I try to
customize toolbar and it doesn't show more.

I try to open the file as "open exclusive" and it doesn't change anything.


Only thing I can do is to right click the form and change it to design form
view and work from there. That's how I can view some codes.
 
There are a number of Access startup options, such as removing toolbars,
that may have been set by the author of your database. You might find
opening the file with the shift key held down does the trick, since this
bypasses those startup options.

Robin
 
Try holding the Shift key down when you open the database.

If you right click on the menu bar, does it allow you to
select other tool/menu bars to display?
 
Thanks for the answer again. Now that I see the Link Table manager, and yes,
I see all the table connections are there.

But when I try to change it, I ask me to browse to other access file. Is
there any way I can make it link to a database on SQL server?

Thank you very much for the help.
 
I'm not sure about the SQL bit, but I would suggest that you make a copy of
file A (just in case), then select each of the linked tables from the
database window in turn, and delete them.

Then go to the File menu>Get external data>Link tables, and at the bottom of
the resulting dialog box, choose ODBC Databases() from the "Files of type"
drop down list.

I think (not sure about SQL) from there you should be able to set up a link
to your SQL database.
 
To add to Robin's reply once the links are made then the connection string
is stored in the hidden access table 'MSysObjects' in the [Connect] column.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
 
I am sorry I am not an experienced user in Access.

But yes, thank you, I already figured out how to connect to SQL server, but
to prevent me to change a lot of codes, I need to delete the original linked
tables.

Unfortunately I still can't figure out how to delete the linked table. The
microsoft help file says it's under tables in Database View. But I can't
swtich to table view in my file A. The option is greyed out.

And where is this "Connect" column?

Thank you for all the help.
 
No, it doens't allow me to choose other menu bars. Only option is "menu bar".

Marshall Barton said:
Try holding the Shift key down when you open the database.

If you right click on the menu bar, does it allow you to
select other tool/menu bars to display?
--
Marsh
MVP [MS Access]


Charlie said:
Thanks for the answer. But the problem is, this file A, when I open it, the
menu bar is very limited. And it only shows the first form. This form, when
you click the buttons, will link to other forms that add/edit data.

There is no "Tools", and from "File", only has "Close". And I try to
customize toolbar and it doesn't show more.

I try to open the file as "open exclusive" and it doesn't change anything.


Only thing I can do is to right click the form and change it to design form
view and work from there. That's how I can view some codes.
Click to expand...
Click to expand...
 
If you open file A with the shift key down you should see a 'form' like
object that you wouldn't have seen before, with the title "Your file name:
Database (Access nnnn file format)", which lists tables, queries, forms etc.
Select Tables from the list and you should see a list of linked tables. To
delete a linked table, just right click the table name and select "Delete".

If you don't delete the existing linked tables before linking to SQL the SQL
tables will be renamed. You need to delete all linked tables first.

Charlie Brown said:
I am sorry I am not an experienced user in Access.

But yes, thank you, I already figured out how to connect to SQL server,
but
to prevent me to change a lot of codes, I need to delete the original
linked
tables.

Unfortunately I still can't figure out how to delete the linked table. The
microsoft help file says it's under tables in Database View. But I can't
swtich to table view in my file A. The option is greyed out.

And where is this "Connect" column?

Thank you for all the help.

Dave Patrick said:
To add to Robin's reply once the links are made then the connection
string
is stored in the hidden access table 'MSysObjects' in the [Connect]
column.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
Click to expand...
Click to expand...
 
Hopefully the table names created on SQL server are identical but just in
case they are not write them down before deleting them. Also know that
you'll likely need to rename the SQL server linked tables anyway since
they'll have 'dbo_' as a prefix to the original name.

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect
 
Then the menu/tool bars are disabled and that simple
aooriach isn't going to do anything for you. I see that you
have gotten past this question by using the Shift key so it
doesn't matter if the bars were disabled or just made
invisible.
 
Back
Top