My application is split in a front-end and with a separate database on a server. Does anybody have a code to open a "search box" type textbox where one can drill down to the server, and link to tables in the database. Its easy when you have Access 2007 installed, but i would like to use this in a "runtime version" please.
thx
5 2038
I got this code from a website athttp://www.aislebyaisle.com/access/vba_backend_code.htm#linktables
Can i adapt it to run from a button? How do i go about it. pls help - Function LinkTables(DbPath As String) As Boolean
-
'This links to all the tables that reside in DbPath,
-
' whether or not they already reside in this database.
-
'This works when linking to an Access .mdb file, not to ODBC.
-
'This keeps the same table name on the front end as on the back end.
-
Dim rs As Recordset
-
-
On Error Resume Next
-
-
'get tables in back end database
-
Set rs = CurrentDb.OpenRecordset("SELECT Name " & _
-
"FROM MSysObjects IN '" & DbPath & "' " & _
-
"WHERE Type=1 AND Flags=0")
-
If Err <> 0 Then Exit Function
-
-
'link the tables
-
While Not rs.EOF
-
If DbPath <> Nz(DLookup("Database", "MSysObjects", "Name='" & rs!Name & "' And Type=6")) Then
-
'delete old link, assuming front and back end table have the same name
-
DoCmd.DeleteObject acTable, rs!Name
-
'make new link
-
DoCmd.TransferDatabase acLink, "Microsoft Access", DbPath, acTable, rs!Name, rs!Name
-
End If
-
rs.MoveNext
-
Wend
-
rs.Close
-
-
LinkTables = True
-
End Function
-
-
-
Sub CallLinkTables()
-
Dim Result As Boolean
-
-
'sample call:
-
Result = LinkTables("C:\fis\fis_datav3.accdb")
-
Debug.Print Result
-
End Sub
-
-
You can code a button, to open a filedialog, then after selecting the file pass the path of the file to the function you have posted.
Depending on your setup a more smooth method could be to search a specific folder/subfolder for all .mdb (or .accdb) files and then display these in a form/combobox allowing the user to select from there.
Now this is just a general direction im pointing you in, because writing code for something like this is not a 5 minute thing, it would take considerably longer time, and I think you need to work on it a bit more yourself.
- The following Code will:
- Open the Microsoft Office File Dialog filtered for Access Databases only (*.mdb).
- Allow you to select a 'single' Access Database.
- Loop through all the Tables in the External Database.
- Dynamically Link only those Tables which are valid (Non-System, Non-Temporary).
- Display in a Text Box exactly which Tables were Linked.
- Any other questions, feel free to ask.
- Code posted for your review.
- Download the Demo (Attachment) that I have created for you to get a good picture of exactly what is going on.
- Relevant Code:
- 'First, set a Reference to the Microsoft Office XX.X Object Library
-
Dim strButtonCaption As String
-
Dim strDialogTitle As String
-
Dim strDBName As String
-
-
'Define your own Captions if necessary
-
strButtonCaption = "Open DB"
-
strDialogTitle = "Select Database to Link"""
-
-
With Application.FileDialog(msoFileDialogFilePicker)
-
With .Filters
-
.Clear
-
.Add "Access Databases", "*.mdb" 'Allow Access Databases only
-
End With
-
'The Show Method returns True if 1 or more files are selected
-
.AllowMultiSelect = False 'Critical Line
-
.ButtonName = strButtonCaption
-
.InitialFileName = vbNullString
-
.InitialView = msoFileDialogViewDetails 'Detailed View
-
.Title = strDialogTitle
-
If .Show Then
-
strDBName = .SelectedItems(1)
-
End If
-
End With
-
-
If strDBName = "" Then Exit Sub 'No DB selected
-
-
'The External Database name is now stored in the variable strDBName
-
Dim wrkJet As DAO.Workspace
-
Dim dbLink As DAO.Database
-
Dim tdf As DAO.TableDef
-
Dim strBuild As String
-
-
Me![txtResults] = "" 'Clear Results Box
-
-
'Create Microsoft Jet Workspace object.
-
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
-
-
'Open Database Object for Shared Use.
-
Set dbLink = wrkJet.OpenDatabase(strDBName, False)
-
-
'Loop thru all Tables in the External Database, but ignore System
-
'Tables and Temporary Tables (MSys*, USys*, ~*)
-
For Each tdf In dbLink.TableDefs
-
If Mid$(tdf.Name, 2, 3) <> "Sys" And Left$(tdf.Name, 1) <> "~" Then
-
'If you get here, Link to the Table
-
DoCmd.TransferDatabase acLink, "Microsoft Access", strDBName, acTable, tdf.Name, tdf.Name, False
-
strBuild = strBuild & "Table " & tdf.Name & " in " & strDBName & " successfully Linked!" & vbCrLf
-
End If
-
Next
-
-
If strBuild = "" Then Exit Sub 'No Tables other than System and/or Temporary
-
-
Me![txtResults] = Left$(strBuild, Len(strBuild) - 2) 'Strip vbCrLf & post Results
-
-
dbLink.Close
-
Set dbLink = Nothing
Thx mr Adezii for the trouble
I seem to have an Access 2007 issue here. I will attach the screen pics.
I got the above code to work eventually and its great (had reference library issues).
My next question:
how do i change the code to work when using "AccessRuntime", with the front-end application? Any ideas?
Sign in to post your reply or Sign up for a free account.
Similar topics
by: LinuxN00b |
last post by:
Hey,
I have a problem linking Xerces-c (Apache's XMLParser)
with my program.
Here is a copy of my makefile.
# CXX is for GNU make, CCC is for Solaris make # Define the default
compiler to...
|
by: Steven T. Hatton |
last post by:
Is there anything that gives a good description of how source code is
converted into a translation unit, then object code, and then linked. I'm
particularly interested in understanding why putting...
|
by: sunil |
last post by:
Hi,
We have lot of c and fortran archive libraries that have complex
dependencies. We have different server tasks that use some of these
libraries. We have developed a tool inhouse that links...
|
by: Ozzy |
last post by:
We've got a CDROM with a Flash Projector movie on it. Within the
Flash projector (which is already burned on CDROM...can't change it),
we have the typical getURL fuctions to open a Web browser and...
|
by: Steven T. Hatton |
last post by:
I just read this in the description of how C++ is supposed to be
implemented:
"All external object and function references are resolved. Library
components are linked to satisfy external...
|
by: randau |
last post by:
Linking to a Targeted Browser Window
I'd like to open reference links to other web sites in a
separate browser window from the browser window hosting my
own web site pages. The Link Target...
|
by: Joerg Glissmann |
last post by:
Hi there,
is there a way to link an Access XP database to a query in a different
database (Access XP as well) like you can do with tables?
My front ends need to operate on different data...
|
by: srikar |
last post by:
what is the difference between static linking & dynamic linking,
what are the advantages of each?
How to perform static linking & Dynamic linking
by using gcc -o liniking will be done , but...
|
by: Tazzy via AccessMonster.com |
last post by:
Hi all, I wonder if anyone can help me with this please. I have set up forms
on my database, one of the fields contains details of e-mail addresses. I am
trying to create a link so that when I...
|
by: nipun sabharwal |
last post by:
void main()
{
int gd=DETECT,gm;
initgraph(&gd,&gm,"c:\\tc\\bgi");
setcolor(GREEN);
settextstyle(4,0,8);
outtextxy(200,50,"TIME");
setcolor(YELLOW);
settextstyle(4,0,8);...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
|
by: Faith0G |
last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome former...
| |