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

KP_SoCal

Board Regular
Joined
Nov 17, 2009
Messages
116
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.


Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,909
Office Version
  1. 365
Platform
  1. Windows
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.

Code:
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.

Code:
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
Code:
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

Board Regular
Joined
Nov 17, 2009
Messages
116
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

Well-known Member
Joined
Jun 3, 2015
Messages
1,909
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,306
Messages
5,527,921
Members
409,793
Latest member
mavrik_stet

This Week's Hot Topics

Top