Query Import Access 2007

[dawnd3]

I am creating a little program in a new database with data from another database. The database is huge, so I want to just import in the subset data that I need so that the new database isn't so huge. In the new database, the program will be a macro with several queries from that data and the user will only have to click a button to get the results. Is it possible for me to have the new database pick up the subset of data (query) from the larger database and import it in each time the macro is run? Would I build the query in the old database and then set it up as an import in the new database? Would it refresh the data each time? I want the simplest solution for the user. i.e. I don't even want to trust him to have to use linked table manager if I can do it all through an import process in the macro.

Thanks,

Dawn

 

[dhookom]

I would simply link the tables from the other database. I'm not sure what you mean by " don't even want to trust him to have to use linked table manager". What would the user have to do that you wouldn't trust?

Duane
Hook'D on Access
MS Access MVP

 

[dawnd3]

He isn't very savvy, so each time I send him an updated database (I am constantly adding data for him remotley) he would have to go in and relink the tables between the two. I was hoping to avoid that. I can't do an automated import of query results?

 

[MajP]

I agree with Duane, but you can do what you are asking without a link to the database using a remote query.

http://www.vb123.com/toolshed/02_accvb/remotequeries.htm

You can also code the link table manager so that the user just clicks a button to link the backend and never goes to the table manager.

 

[MajP]

Each time you send him and updated database, you could add the code to link only to the needed tables. He would never have to go to the link table manager. However, if you are sending him and updated DB, the links should already exist so if you are using UNC then why would he have to go to the link table manager or have code relink?

 

[dawnd3]

I see what you are saying. But I would have it linked on my machine and the path would be different on his machine. Can you tell me how to code it so that he doesn't have to use linked table manager to re-link?

Thanks guys!!!!

Dawn

 

[MajP]

If you type a UNC in instead of a drive letter path, it will be the same in your machine and his machine. You should never except for personal use, use the drive letter path.

 

[MajP]

Sorry here is one link on how to write the UNC instead of drive letter

http://www.uwplatt.edu/oit/terms/uncpath.html

 

[MajP]

If you still want to write code (which is probably unecessary)

http://www.mvps.org/access/tables/tbl0009.htm

 

[dawnd3]

Thank you!

 

[dawnd3]

Sorry, I think maybe I didn't explain correctly. I don't have a shared drive with this person. We are just emailing the files back and forth. I don't think the UNC method will work in this case.

 

[MajP]

Is that by choice, or do you really not have a shared network? Kind of old school.

So are you just sending him an updated backend with tables, or are you sending him a front end, or both?

If you are just sending him the backend then this is confusing. Where do you plan to put the code to link to the new backend you send him? Normally that code is in the front end. Assume he has a front end, and you only send a backend. Assume he was linked to tables A,B,C but with the new set of data you want him to also link to table D. If you are only providing a backend then the updated code to D would have to be in the backend. This can be done by having a his front end call a procedure in the backend. Never seen it done this way, but it is doable.

So i think what you really need is code in his Front end that says link to any table in the backend. So when you send him something it deletes his links and relinks to the database he points to using a common dialog box. Something like.

Private Sub LinkTables(strDataLinkPath)
'Delete the linked tables in the current Front End and
'link the current Front End DB to the
'the called for Back End Tables.

Dim dbFE As DAO.Database
Dim tdfFE As DAO.TableDef
Dim intK As Integer
Dim intProgressValue As Integer
Dim intResponse As Integer
Dim dbBE As DAO.Database
Dim tdfBE As DAO.TableDef
Dim frm As Access.Form
   Set dbFE = CurrentDb
'make sure all forms that could be bound to any tables
'are closed.
    For Each frm In Forms
      DoCmd.Close acForm, frm.Name
    Next frm
    
    With dbFE.TableDefs
       For intK = .Count - 1 To 0 Step -1
           If .Item(intK).Connect <> "" Then
           .Delete .Item(intK).Name
           End If
       Next intK
   End With

   Set dbBE = OpenDatabase(strDataLinkPath)
   For Each tdfBE In dbBE.TableDefs
       If Not Left(tdfBE.Name, 4) = "MSys" Then
       'create a table definition in FrontEnd matching table definiton in BackEnd as linked tables.
       Set tdfFE = dbFE.CreateTableDef(tdfBE.Name)
           tdfFE.Connect = ";DATABASE=" & strDataLinkPath
           tdfFE.SourceTableName = tdfBE.Name
       'add each table to the table defintion collection
       dbFE.TableDefs.Append tdfFE
       intProgressValue = intProgressValue + 1

       End If
   Next tdfBE
   dbFE.TableDefs.Refresh

   Set tdfBE = Nothing
   dbBE.Close:   Set dbBE = Nothing
   Set tdfFE = Nothing
   Set dbFE = Nothing

   MsgBox "All tables have been properly linked.", vbExclamation
End Sub

Public Sub TestLinkAll()
  'fgetfilename is a common dialog I use
  LinkTables fGetFileName
End Sub

 

[dawnd3]

I think the easiest way is that I can just create a duplicate path on my machine as the path he has for his database files. Or at least the lazy way. LOL

 

[dawnd3]

I just work out of my house and he works out of his house in another state. I send him the whole database, it isn't split into back end and front end because that was how he already had it when I took over maintaining his program. So, I am not linking tables from front to back I am linking from one database to another that will use the same data for another unrelated project.

Dawn