Add records to a table referencing a table in another database - DAO (1 Viewer)

[crhh1989]

Hello,

I am using Access 2010 and I currently use a command button on a form to add new records to a table using data that the user has entered into the form using the code below:

Although this is pretty self-explanatory, here is a key for reference:
Me.lstFacilities = ListBox
Me.cboMeasure = ComboBox
Me.cboYesNo = ComboBox
Me.txtTarget = TextBox

Private Sub cmdAddMet_Click()
Dim DB As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL As String
Dim i As Integer

Set DB = CurrentDb

strSQL = "SELECT * FROM tblFacRelationship"

Set RS = DB.OpenRecordset(strSQL)

For i = 0 To lstFacilities.ListCount - 1
    If lstFacilities.Selected(i) = True Then
        RS.AddNew
        RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i)
        RS!MEASUREMENT_PERIOD = Me.cboMeasure
        RS!GOALMET_NAME = Me.cboYesNo
        RS!GOAL = Me.txtTarget
        RS.Update
    End If
Next i

RS.Close
Set RS = Nothing
Set DB = Nothing

End Sub

This works great but I would like to be able to pull in data from another database based off of Me.lstFacilities.Column(1, i) which is the FACILITY_ID field and is located in the other database's table. I thought about adding in another string variable(strSQL1) and opening up a separate recordset and database:

Set DB1 = OpenDatabase("\\server\otherdb.accdb")
strSQL1 = "SELECT [FieldName] FROM [tblOtherDatabase] IN '\\server\otherdb.accdb'"
Set RS1 = DB1.OpenRecordset(strSQL1)

However, I'm not sure where to start pulling in the data from the [fieldname] in the [tblOtherDatabase] when I start the loop below:

For i = 0 To lstFacilities.ListCount - 1
    If lstFacilities.Selected(i) = True Then
        RS.AddNew
        RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i)
        RS!MEASUREMENT_PERIOD = Me.cboMeasure
        RS!GOALMET_NAME = Me.cboYesNo
        RS!GOAL = Me.txtTarget
        RS.Update
    End If
Next i

Is it even possible to do this? Please let me know if my question is ambiguous in any way. Thanks for any help!

 

[spikepl]

If your data of interest is in the second column of the combo, then you are done - just get it from there.

myValueOfInterestInFirstColumn=Me.lstFacilities.Column(0,i)
myValueOfInterestInSecondColumn=Me.lstFacilities.Column(1, i)

But note that the point of storing ID's and NOT both ID's and the values those ID's refer to is to retain normalization so as not to store the same data twice, and to get the data updated automatically when it gets changed at source.

Further

strSQL = "SELECT * FROM tblFacRelationship"

loads the entire table wtih all previous values, but for no purpose, since you'll only be adding new values here.

Therefore:

strSQL = "SELECT * FROM tblFacRelationship WHERE 1=-1"

is much better - it loads the data schema into the recordset but no previous data, since no records fulfil the criterion of 1=-1 !

 

[crhh1989]

Thanks for the tips! I am always looking for ways to make my code more efficient! The data of interest is actually in another database altogether - sorry I haven't done the best job of explaining this...

If...

DatabaseA = Current database (the one that the user is adding records into)

DatabaseB = External database that houses a PerformanceScore column that needs to be pulled into DatabaseA - This database also has a FACILITY_ID listed in the same table

Basically, the DatabaseB has a table in it with a column that I need data from based on the corresponding FACILITY_ID referenced in the control in DatabaseA (found in Me.lstFacilities). I only want to use the FACILITY_ID to ensure that Access pulls the correct performance score for the corresponding facility.

Honestly, DatabaseB is issued by the government to our company and using it for anything is no picnic since it's so poorly designed (they don't use a single form). Using it efficiently is almost impossible because the design and field names change almost every month we receive it. Thus, the reason for using this approach...

 

[mh123]

can you not just link table to database B?

 

[crhh1989]

I could but unfortunately, the names of the database that we are sent varies per month because the data in it reflects the month that it's name is for. I have thought of creating a separate backend database that holds all of the aggregate data from all of our files but with the structure changing constantly, I think it would just end up being a lot of work.

Not to mention, there are at at least 5 other databases with totally different project-related information that we receive from separate government entities and have a completely different structure as well.

 

[MarkK]

If I was periodically receiving critical data that was irregularly structured (dirty), I would create a consistent in-house structure and "upload" (clean) the irregular data, effectively eliminating the irregularity. Then you are free to reliably automate other in-house consumers to draw data from your in-house, consistently structured, "purified" source.

 

[crhh1989]

That is something that we are looking at but in the meantime, is there no way to modify the code above to import data? Ultimately, it would be desirable to allow the user to select a file using the FileSystemObject, pick a table from a separate form, and then select the column they wish to import data from in another form.

I can do anything like create the variables, etc. but I would like to test this by hard coding a column in to pull in data. I just have no idea how to modify the loop to allow me to do it.

 

[MarkK]

What does the loop need to do that it doesn't now? You will insert one value, correct, from your remote database, the FacilityID, so I would expect to see something like . . .

dim FacilityID as long
[COLOR="Green"]'GetRemoteFacilityID() is the function, not shown, where your user selects the remote db
'selects the table and field, and a single value is returned
[/COLOR]FacilityID = GetRemoteFacilityID()

For i = 0 To lstFacilities.ListCount - 1
    If lstFacilities.Selected(i) = True Then
        RS.AddNew
[COLOR="Green"]        'here we insert that FacilityID into each added row[/COLOR]
        [COLOR="DarkRed"]RS!FacilityID = FacilityID[/COLOR]
        RS!RELATIONSHIP_ID = Me.lstFacilities.Column(0, i)
        RS!MEASUREMENT_PERIOD = Me.cboMeasure
        RS!GOALMET_NAME = Me.cboYesNo
        RS!GOAL = Me.txtTarget
        RS.Update
    End If
Next i

Is that the loop modification you're talking about?

 

[crhh1989]

Okay, I think that's on the right track. However, what I actually need is the value in the [PerformanceScore] column in tblScores in the DatabaseB.

Below is how the 2 databases are structured. What I need is in red:

DatabaseA:
tblFacilities: FACILITY_ID
tblFacilities: FACILITY_NAME

DatabaseB: (one issued by the government)
tblScores: FACILITY_ID
tblScores: PerformanceScore (that contains the matching score for the facility based on the FACILITY_ID)

So, I don't actually need the FACILITY_ID. I need the correct PerformanceScore added to the new record created in DatabaseA from external DatabaseB based on the matching Facility_Id that is in the tblScores column of DatabaseB.

I feel like my question is still confusing so please let me know how I can clarify if it is.

 

[MarkK]

You have multiple problems to solve here, and you are going to need to find a way to separate them and focus on one at a time. You have a loop that saves multiple records, and into this loop you need to inject a single value, correct? So solve that problem first, as a suggestion. Don't worry about how that single value is produced for now.

But it's your thread. If your question is not clear, refine it as you see fit.

 

[crhh1989]

Thanks, if you're understanding the issue (which it seems you are) then I think the question is clear. I will play around with this and add the solution here. Dlookup would probably be an "easy" way of achieving what I am looking for but I think that recordsets would be far more efficient.