Create Recordset Of A Table In A Different Database

[BenSC]

Hi All,
I'm trying to create a split, back end/front end database. I've designed the database and have used the Access database splitter to create the back/front end parts. Obviously my recordsets aren't working now as the tables they are created from are in a different database.

Unfortunately I'm not sure how to create a recordset of a table in a different Access database. Any help would be much appreciated.

Thanks
BenC

 

[KarMac21]

The easiest way to do this would be to link the tables from your back end to your front end. If you didn't want to do this, you would have to create an ADO Connection to your back back end and use that connection for your recordset.

Hope this helps. If you need more help, please let me know.

Shane

 

[BenSC]

I did try using the linked table as a base for my recordset, but found that the Seek command couldn't be used on a linked table.
I've worked around that now by using a dynaset recordset and using the findfirst command.

Thanks for the help

BenC

Private Sub CmdAdd_Click()
'Create The Site Selected In The Text Box

Dim db As Database
Dim rst As Recordset
Dim strSiteRef As String
Dim ctrlSiteRefLower As Control

Set db = CurrentDb()
Set rst = db.OpenRecordset("SELECT SiteReference FROM tblSite", dbOpenDynaset)

'Validate For No Entry In The Text Box. If There Is An Entry Set It To The Variable ctrlSite
If IsNull(Forms!frmSiteMenu!Site) Then
MsgBox "You Must Enter A Site Reference To Create"
DoCmd.GoToControl "Site"
Exit Sub
Else
Set ctrlSiteRefLower = Forms!frmSiteMenu!Site
strSiteRef = UCase(ctrlSiteRefLower)
End If

rst.FindFirst "[SiteReference] = '" & strSiteRef & "'"

If rst.NoMatch Then
DoCmd.RunSQL "INSERT INTO tblSite ( SiteReference ) SELECT '" & strSiteRef & "' as Expr1"
DoCmd.CopyObject , strSiteRef, acTable, "tblSampleSite"
MsgBox "Site " & strSiteRef & " Has Been Created", , "Create Site"
Else
MsgBox "Site " & strSiteRef & " Cannot Be Created As It Already Exists", , "Error"
End If

rst.Close
DoCmd.Requery "Site"

End Sub

 

[NorthNone]

I have the same problem except that if I find the record, I need to go to it on my form. SEe the asterisks toward the end of the code copied from above.

If rst.NoMatch Then
DoCmd.RunSQL "INSERT INTO tblSite ( SiteReference ) SELECT '" & strSiteRef & "' as Expr1"
DoCmd.CopyObject , strSiteRef, acTable, "tblSampleSite"
MsgBox "Site " & strSiteRef & " Has Been Created", , "Create Site"
Else
**GO TO THE RECORD I FOUND ON MY FORM THAT IS INDEXED BY
**LAST NAME BUT THAT I NEED TO SEARCH BY Social Security **Number
End If