QueryDef to link form to tables 1

[Roblin]

I have a split DB that contains a table for each user in the backend. The frontend contains code in a module that grabs the network ID of the user:

Function ap_GetUserName() As Variant
Dim strUserName As String
Dim lngLength As Long
Dim lngResult As Long
strUserName = String$(255, 0)
lngLength = 255
lngResult = wu_GetUserName(strUserName, lngLength)
ap_GetUserName = Left(strUserName, InStr(1,strUserName, Chr(0)) - 1)
strUserName = ap_GetUserName
End Function

I was told that I can create a QueryDef from code so that without altering the form on the frontend, it will save the data entered on the form to the corresponding user's table in the backend based on the network ID function. The form the data is input on is called "frmEntry". The corresponding table for my network ID is tblRDL with my network ID being RDL. Does anyone know how to do this? Thanks in advance. I'm still learning what a QueryDef is.

 

[Remou]

Hi
I think what you are looking at is an On Open event on the form:

Dim vTableName
vTableName = "tbl" & strUserName
Me.RecordSource = vTableName

Or something similar as a query.
For this you would need a table called tblUserName for each user. You would also need to link the table programmatically, if the link did not already exist. You could even create the table programmatically, if that did not exist. Linking would look something like this:

vSource = "tbl" & strUserName
vDestination = "tbl" & strUserName
DoCmd.TransferDatabase acLink, "Microsoft Access", _
  "C:\MyBE.mdb", acTable, vSource, vDestination, False

 

[Roblin]

Remou,

Thank you so much. I just did not know how to do it, but your suggestion totally worked. I haven't tried it on anyone else's machine yet, but the changes worked on mine and I took the record source out of the form's properties. So, I'm guessing it will work on someone else's machine. Thanks again!!!

 

[Remou]

Most welcome.

 

[Roblin]

Hi Remou,

Another question. In the frontend, everytime I go to the entry form, a new pointer is created. So, I have several pointers in the table section of the frontend. They look like: "tblRDL1", "tblRDL2", "tblRDL3" and so on. I am able to delete them manually. The original tblRDL will not delete, but I don't think it needs to be deleted. Any ideas?

 

[snotmare]

Hello Roblin!

If you are wanting to delete the temporary tables you create, you could execute a statement such as this:

docmd.DeleteObject(acTable, "tbl" & strUserName)

Good luck!

He who has knowledge spares his words, and a man of understanding is of a calm spirit. Even a fool is counted wise when he holds his peace; when he shuts his lips, he is considered perceptive. - King Solomon

 

[Remou]

Hi
I think it might be best to check if the table exists before creating the link. For example

On Error Resume Next
Dim rs
Dim vTableName
vTableName = "tbl" & strUserName

Set rs = CurrentDb.OpenRecordset(vTableName)
'Microsoft Jet database engine cannot find the input table or query
If Err.Number = 3078 Then
  'Insert link table code here
  Err.Clear
End If
Set rs = Nothing

There may well be be a better way of doing this.

 

[Roblin]

Thanks for the suggestion snotmare. I got your suggestion to work, but only if I specified the table name for each user.

Remou,

Again your suggestion worked. It won't creat a new link if one already exists. I really appreciate it. Have a good evening.