FDialog Dynamic Table Seleting

[LB79]

Hi everyone,
I've come across a problem with importing access tables from one mdb to another in VB.
Usually I use FDialog and TransferDatabase.
The item im working on right now needs to import a table from 1 mdb (MDB1) to another (MDB2), but the table name in MDB1 changes daily.
Can anyone tell me how I can make this dynamic so that whatever the table name in MDB1 is, it will import.
(MDB1 only ever has one table).
This is the line of code I would normally use:

DoCmd.TransferDatabase acImport, "Microsoft Access", Me!txtPath, acTable, "TableName", "tbl_MyTable”, DataOnly, False

Thanks (hope that makes sense)

 

[vbaInet]

Having to change a table name daily sounds like there's a problem with your design. Could you explain the situation?

It also seems to me that your users have access to the tables?

 

[LB79]

Hi and thanks for replying.
The mdb table to be imported is created by a program and part of the table name is the date (YYYYMMDD), hence the name of the table being different each day. At the moment useres are manually importing the mdb table, then renaming it. I want to automate this so that the program imports the only table, rather then a named table.

 

[vbaInet]

So what program makes this table?

It is possible to identify the table if the first couple of letters of the table name are unique to the rest of the other table names. Can you make it so?

 

[boblarson]

This code should do it for you.

Function GetTableName(strDbPath) As String

    Dim db As DAO.Database
    Dim tdf As DAO.TableDef

    Set db = OpenDatabase(strDbPath)

    For Each tdf In db.TableDefs
        If InStr(1, tdf.Name, Format(Date, "YYYYMMDD"), vbTextCompare) > 0 Then
            GetTableName = tdf.Name
            Exit For
        End If
    Next

    If GetTableName = "" Then
        GetTableName = "Not Found"
    End If

db.Close
Set db = Nothing

End Function

You can use your dialog to return the file name which then you pass to that function. You can assign the value of the function to a variable and check to see if it says "Not Found" and if so abort the transfer. If it finds it then you can use that variable in your code instead of the file name.

 

[vbaInet]

I was thinking something along the lines of this perhaps:

dim tdfName as string

tdfName = Nz(DLookup("[Name]", "MsysObjects", "Type=1 AND Right(Name, 8) = '" & format(Date, "yyyymmdd") & "'"), "")

If len(tdfName)<> 0 then
     Docmd.transfer...
else
    msgbox "not found"
end if

Same as Bob's really, just worded differently.

 

[boblarson]

I was thinking something along the lines of this perhaps:

dim tdfName as string

tdfName = Nz(DLookup("[Name]", "MsysObjects", "Type=1 AND Right(Name, 8) = '" & format(Date, "yyyymmdd") & "'"), "")

If len(tdfName)<> 0 then
     Docmd.transfer...
else
    msgbox "not found"
end if

Same as Bob's really, just worded differently.

Well, still quite a bit far off of mine as you didn't go anywhere near the external database (which is what is needed as well)

 

[vbaInet]

Ahhh.... I didn't notice that bit.