Dynamically link tables in Access 97 thru VB?

[JoeyCode]

Hey Everybody,

I'm trying to figure out the easiest, least hassle way to solve this issue I've got, maybe some of you can help.

I'm creating an attendance system for use in small schools. I'm using VB6 for the user interface and Access 97 for the database, connecting with DAO. What I want to do is have a single Access DB that contains only the data tables (call this the "DATA" DB), and have another Access DB that links the tables from the DATA DB and contains all the queries and whatever other junk I need to put into Access (call this the "MAIN" DB). I want to do this so that if any of the stuff in MAIN needs to be updated, I can do so without disturbing the actual DATA.

Originally I wanted to have just a single MAIN DB that all the machines connected to, but since Access stores the full path of the linked DATA tables, only the machine that has the DATA DB on it (essentially the file server) would be able to use it. So this means each machine needs its own copy of the MAIN DB with its own unique linking info. The problem with this is that the only way I can find to link tables in Access is to do it within Access itself. This is a problem because I can't pre-link the tables due to the fact that I will have no idea ahead of time where the DBs will be located, and Access will not be available to do linking at installation time. But even if it was, this doesn't allow the databases to be moved very easily.

What I'd like to be able to do is to have my VB front-end, that knows the locations of all the DBs, to be able to tell the MAIN DB at any time to unlink and relink the DATA DB tables. However, I haven't been able to find a way to do this. I'm thinking it's not possible, and I wonder how others have gotten around these issues. There's so little information available on this topic that I've been able to find out there that I'm considering just consolidating the DATA and MAIN DBs together and be done with it. But I don't want to do that if there are any other options. Anybody know of a way I can dynamically link tables, or some other way to solve these issues without having to go to a single DB or immensely complicate the installation and maintanence of this system?

Thanks for your help,
-JoeyCode

[Clunietp]

Just put your main DB on a file server and link the table with a UNC pathname, so all of the other machines will reference \\ServerName\DirName\File.mdb.

If you want to dynamically add a link to a table, I found this in the DAO help:

This example uses the Connect and SourceTableName properties to link various external tables to a Microsoft Jet database. The ConnectOutput procedure is required for this procedure to run.

Sub ConnectX()

	Dim dbsTemp As Database
	Dim strMenu As String
	Dim strInput As String

	' Open a Microsoft Jet database to which you will link
	' a table.
	Set dbsTemp = OpenDatabase("DB1.mdb")

	' Build menu text.
	strMenu = "Enter number for data source:" & vbCr
	strMenu = strMenu & _
		"   1. Microsoft Jet database" & vbCr
	strMenu = strMenu & _
		"   2. Microsoft FoxPro 3.0 table" & vbCr
	strMenu = strMenu & _
		"   3. dBASE table" & vbCr

strMenu = strMenu & _
		"   4. Paradox table" & vbCr
	strMenu = strMenu & _
		"   M. (see choices 5-9)"

	' Get user's choice.
	strInput = InputBox(strMenu)

	If UCase(strInput) = "M" Then

		' Build menu text.
		strMenu = "Enter number for data source:" & vbCr
		strMenu = strMenu & _
			"   5. Microsoft Excel spreadsheet" & vbCr
		strMenu = strMenu & _
			"   6. Lotus spreadsheet" & vbCr
		strMenu = strMenu & _
			"   7. Comma-delimited text (CSV)" & vbCr

strMenu = strMenu & _
			"   8. HTML table" & vbCr
		strMenu = strMenu & _
			"   9. Microsoft Exchange folder"

		' Get user's choice.
		strInput = InputBox(strMenu)

	End If

	' Call the ConnectOutput procedure. The third argument
	' will be used as the Connect string, and the fourth
	' argument will be used as the SourceTableName.
	Select Case Val(strInput)
		Case 1
			ConnectOutput dbsTemp, _
				"JetTable", _
				";DATABASE=C:\My Documents\Northwind.mdb", _

"Employees"
		Case 2
			ConnectOutput dbsTemp, _
				"FoxProTable", _
				"FoxPro 3.0;DATABASE=C:\FoxPro30\Samples", _
				"Q1Sales"
		Case 3
			ConnectOutput dbsTemp, _
				"dBASETable", _
				"dBase IV;DATABASE=C:\dBASE\Samples", _
				"Accounts"
		Case 4
			ConnectOutput dbsTemp, _
				"ParadoxTable", _
				"Paradox 3.X;DATABASE=C:\Paradox\Samples", _
				"Accounts"
		Case 5
			ConnectOutput dbsTemp, _

"ExcelTable", _
				"Excel 5.0;" & _
					"DATABASE=C:\Excel\Samples\Q1Sales.xls", _
				"January Sales"
		Case 6
			ConnectOutput dbsTemp, _
				"LotusTable", _
				"Lotus WK3;" & _
					"DATABASE=C:\Lotus\Samples\Sales.xls", _
				"THIRDQTR"
		Case 7
			ConnectOutput dbsTemp, _
				"CSVTable", _
				"Text;DATABASE=C:\Samples", _
				"Sample.txt"
		Case 8
			ConnectOutput dbsTemp, _
				"HTMLTable", _

"HTML Import;DATABASE=http://" & _
					"www.server1.com/samples/page1.html", _
				"Q1SalesData"
		Case 9
			ConnectOutput dbsTemp, _
				"ExchangeTable", _
				"Exchange 4.0;MAPILEVEL=" & _
					"Mailbox - Michelle Wortman (Exchange)" & _
					"|People\Important;", _
				"Jerry Wheeler"
	End Select

	dbsTemp.Close

End Sub

Sub ConnectOutput(dbsTemp As Database, _
	strTable As String, strConnect As String, _

strSourceTable As String)

	Dim tdfLinked As TableDef
	Dim rstLinked As Recordset
	Dim intTemp As Integer

	' Create a new TableDef, set its Connect and
	' SourceTableName properties based on the passed
	' arguments, and append it to the TableDefs collection.
	Set tdfLinked = dbsTemp.CreateTableDef(strTable)

	tdfLinked.Connect = strConnect
	tdfLinked.SourceTableName = strSourceTable
	dbsTemp.TableDefs.Append tdfLinked

	Set rstLinked = dbsTemp.OpenRecordset(strTable)

Debug.Print "Data from linked table:"

	' Display the first three records of the linked table.
	intTemp = 1
	With rstLinked
		Do While Not .EOF And intTemp <= 3
			Debug.Print , .Fields(0), .Fields(1)
			intTemp = intTemp + 1
			.MoveNext
		Loop
		If Not .EOF Then Debug.Print , "[additional records]"
		.Close
	End With

	' Delete the linked table because this is a demonstration.
	dbsTemp.TableDefs.Delete strTable

End Sub

[JoeyCode]

Thanks Clunietp. There may or may not be a dedicated file server available, so I'm not sure the UNC option would work (it also wouldn't allow flexibility). But thanks for pointing me in the right direction code-wise.

-JoeyCode