Location of table so I can link it?

[cloudforgiven]

Hi I am importing some databases from access 1997 to 2003. When I do the import an convert it to 2003 format certain forms dont work, meaning the queries the forms run break(They do work in access 1997 so I know they aren't corrupted or anything). Now these queries are linked to some tables, and instead of importing everything an hoping it works I now deduced that I have to import the tables, then link the tables then import the queries an everything else, so it would work. My issue now is I have the link table manager open in access 1997 so I know what tables need to be linked, however I DO NOT KNOW WHERE to link these tables to? Is there a way for me trace where these tables are link to which DB? I am not the owner who created the DB that was made like 7 or 8 years ago?


edit**Ok I tried to link a table to a random DB, and for some reason even though its link it wont show up in the table manager??



Thank you.

[ItsMe]

You upgraded to 2002-2003 format? Your forms are in one file and the tables are in another file? What version of Office are you using to connect to the backend?

[cloudforgiven]

Yes it's converted to 2002-2003 format. Also how can the forms be in a different file from the tables? It's 1 DB file that was created when I converted. Also the backend is in 1997 format an I haven't converted that yet because I don't have permission to import it even though I've got admin on the DB

[ItsMe]

Yes it's converted to 2002-2003 format. Also how can the forms be in a different file from the tables? It's 1 DB file that was created when I converted. Also the backend is in 1997 format an I haven't converted that yet because I don't have permission to import it even though I've got admin on the DB

It seems you are making conflicting statements. Access is a desktop database. Because Access is of this type of technology, developers tend to split the database; place queries, forms, reports, modules in one file (the frontend) and place the tables in another file (the backend).

When you have a split DB, you will need to "connect" to the tables that are located in the backend from the front end. Since your issue seems to be around "connecting", it is important to understand what you are trying to connect.

[cloudforgiven]

Ahh ok..ok. I see. It seems that I was converting the front end and trying to "connect/link" it to the backend), but if you say the backend contains the tables, why does the front end also have tables? Since I imported tables from it to the new DB? Also I know the DB is split because I contacted the guy who made the DB and he said that, but he cant really help me because he doesn't really remember what he did(7, 8 years ago).

[ItsMe]

Just because it is a Frontend, does not mean it cannot have tables. It just means that the data is stored centrally, elsewhere.

Having said that, if you look at the FE file, you should see tables in the Table section of the Navigation Pane (Nav Pane is something associated with newer versions of Microsoft Access). However, the tables you see in the FE file should be "Linked".

There are ways to manage linked tables via VBA code. As you may be aware, there is a Wizard, the Linked Table Manager, also.

It sounds like the first thing you need to do is understand where the data is and what you are trying to upgrade (version-wise).

[cloudforgiven]

Ok, I will try to import the backend tables and connect it to the front end, and then import everything else and let you know if I got any issues. I think your explanation of what the front end and back end contains helps me alot. Please look out for this thread cause I will retry the conversion Friday, since I have school today and tomorrow.

can you tell me how I would use VBA code to find out what tables are connected where? I am not an ecpert on vba but I do have c++ experience.

[ItsMe]

I do not know if I follow your methodology for Importing. Just make sure you keep track of your original files (work on copies) and where your data gets imported to.

As for VBA code, you can go after the Connect property of a DAO.TableDef object. Here is a snippet where I iterate a list of known table names to update the Connect property with the correct file path to the backend. The file path is strPath.

While rs.EOF = False
    If InStr(strPath, rs![FileName]) Then
        Set T = d.TableDefs(rs![TableName])
    '    Debug.Print T.Name
        T.Properties("Connect") = ";DATABASE=" & strPath
        T.RefreshLink
        
    End If 'instr
    
rs.MoveNext

[ssanfu]

I found a viewer years ago and I used it in A2K... and am still using in A2010. Just one form, a text box and a little code.
Open the db, then open the form.

Just one form, a text box and a little code (Copyright included).
Import the form to any dB. Only displays linked tables.

[cloudforgiven]

I do not know if I follow your methodology for Importing.

Yes sorry about this I wasn't very specific. I'm sure your thinking why I cant just open the access 97 DB in 2003 directly then convert it that way. The reason is access 2003 gives me permissions errors when I try to open the DB directly even though I have admin on the DB. So importing is a way to bypass this issue. The big picture is getting these 1997 DB to open in 2007. So I gotta convert to 2003 then use 2007 to open it an convert it to its own format.

As for importing it to 2003 I have to do it in a process(tables first(from the BACKEND), then link tables(to front end), then queries, forms and everything else from the front end) otherwise I will get errors opening the converted DB in 2003 and therefore 2007.

I know I will get errors because I initially just imported the DB FRONT END ONLY and converted that to 2003 but as I said in my first not everything works.
I did the front end only because I was under the impression it contained everything, but since you explained everything I understand what I have to do.

[ItsMe]

OK, it sounds like you have a plan. And yes, there is more than one way to get into the other file. If you cannot get the Import Wizard to do the work for you, you can rebuild stuff. Just keep a copy of the originals in a safe place. This way, you can always go back and use a different approach.

[ItsMe]

@cloudforgiven
Did you see Steve's post #9 ? This seems like a good opportunity to understand different approaches.

[cloudforgiven]

When I use that viewer on the FRONT END DB this is what I get:


So it appears that the tables are linked to DB from all over the computer an not just my BACK END. Actually now I'm begining to question if the BACK END that I have is really the BACK END and not just another random DB.

[ssanfu]

I'm glad the form helped (?) you, but you really seem to have your work cut out for you.

Good luck.......

[cloudforgiven]

WAIIIT!!!! Since some of the forms are not working is it possible to find out what TABLES and QUERIES are connected to THOSE forms?? That way I wont have to re-link all the tables, I will only re-make an re-link the tables that connect to the forms that are NOT working? Since some of the forms are indeed functioning but not all of them?

HELP!!!!