Using a function to eliminate redundancy in sub routine...

[KP_SoCal]

I'm using a sub routine to import specified tables from other databases (see below). As you can see, I'm writing a lot of redundant lines of code. I'm sure I could simplify this with a function, but I'd really appreciate a little guidance on how to accomplish this. As always, thanks for any help.

P.S. On a side note, I'm also running another code that ensures each of the dataobjects listed below are deleted before linking them.

Sub ImportAccessTables()

Fpath = Environ("USERPROFILE")

    DoCmd.TransferDatabase TransferType:=acLink, _
        DatabaseType:="Microsoft Access", _
        DatabaseName:=Fpath & "\Documents\databases\CustomerMaster.accdb", _
        ObjectType:=acTable, _
        Source:="tblAllCustomers", _
        Destination:="tblAllCustomers"
        


    DoCmd.TransferDatabase TransferType:=acLink, _
        DatabaseType:="Microsoft Access", _
        DatabaseName:=Fpath & "\Desktop\Sources.accdb", _
        ObjectType:=acTable, _
        Source:="tblSources", _
        Destination:="tblSources"


    DoCmd.TransferDatabase TransferType:=acLink, _
        DatabaseType:="Microsoft Access", _
        DatabaseName:=Fpath & "\RawData.accdb", _
        ObjectType:=acTable, _
        Source:="tblRawData", _
        Destination:="tblRawData"
End Sub

 

[Micron]

First, I'd advise against repeatedly deleting and recreating tables - even linked ones. You are just asking for corruption IMHO. If you insist and this is all the code you have for this, then there's not much to be gained in terms of the number of code lines, but it might be a good learning opportunity for you. I'm a believer in doing repetitive steps via subs or functions as long as there's a gain in terms of performance, reduction in editing steps when additions/changes are made, or when there's a variation in information that needs to be passed each time the operation is run in a successive manner, as long as the gain warrants the effort.

In your case, you'd need 3 variables that you don't have now, and need to reset them after each iteration, assuming the source and destination values might differ one day.

Dim dbName As String, Srce As String, Dest As String

dbName = Fpath & "\Documents\databases\CustomerMaster.accdb"
Scrce = "tblAllCustomers"
Dest = "tblAllCustomers"
DoMyThing(dbName, Srce, Dest)

dbName = Fpath & "next.accdb"
Scrce = "nextTable"
Dest = "nextTable"
DoMyThing(dbName, Srce, Dest)

repeat as necessary (note that I used aliases to represent your longer parameters)


Sub DoMyThing(dbName As String, Srce As String, Dest As String)

DoCmd.TransferDatabase TransferType:=acLink, _
        DatabaseType:="Microsoft Access", _
        DatabaseName:=dbName 
        ObjectType:=acTable, _
        Source:= Srce, _
        Destination:= Dest
End Sub

You could trim this further by not using what I'd call Excel type vba. Probably not a good name for it, but it's where I first saw, and continue to see, parameters passed that way.

DoCmd.TransferDatabase(acLink,"Microsoft Access",dbName, acTable, Srce, Dest)

The variable names for the sub and calling sub just happen to be the same here. That's not necessary. In fact, the variable names are not really required in the first sub now that I think of it. You could eliminate them and just pass the full string that is the parameter, such as

DoMyThing(Fpath & "\Documents\databases\CustomerMaster.accdb", "tblAllCustomers", "tblAllCustomers")

It all depends on your preference, but it would make the DoCmd part much longer.

 

[KP_SoCal]

This looks terrific! Thank you!! I won't have a chance to test it out for another week or so, but if I run into any issues, I will post it here.

So thanks again, it definitely looks like a much more efficient approach than my original plan.

 

[Micron]

You're welcome. In retrospect, I'm not sure why I thought this

it would make the DoCmd part much longer.

Not seeing that now. I must have been thinking of the function call, not the DoCmd method syntax.