473,326 Members | 2,438 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

How to link a front-end application to a server in runtime version?

547 512MB
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
Jan 20 '11 #1
5 2038
neelsfer
547 512MB
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

Expand|Select|Wrap|Line Numbers
  1. Function LinkTables(DbPath As String) As Boolean
  2. 'This links to all the tables that reside in DbPath,
  3. '  whether or not they already reside in this database.
  4. 'This works when linking to an Access .mdb file, not to ODBC.
  5. 'This keeps the same table name on the front end as on the back end.
  6. Dim rs As Recordset
  7.  
  8.     On Error Resume Next
  9.  
  10. 'get tables in back end database
  11.     Set rs = CurrentDb.OpenRecordset("SELECT Name " & _
  12.                                     "FROM MSysObjects IN '" & DbPath & "' " & _
  13.                                     "WHERE Type=1 AND Flags=0")
  14.     If Err <> 0 Then Exit Function
  15.  
  16. 'link the tables
  17.     While Not rs.EOF
  18.         If DbPath <> Nz(DLookup("Database", "MSysObjects", "Name='" & rs!Name & "' And Type=6")) Then
  19.             'delete old link, assuming front and back end table have the same name
  20.             DoCmd.DeleteObject acTable, rs!Name
  21.             'make new link
  22.             DoCmd.TransferDatabase acLink, "Microsoft Access", DbPath, acTable, rs!Name, rs!Name
  23.         End If
  24.         rs.MoveNext
  25.     Wend
  26.     rs.Close
  27.  
  28.     LinkTables = True
  29. End Function
  30.  
  31.  
  32. Sub CallLinkTables()
  33. Dim Result As Boolean
  34.  
  35. 'sample call:
  36. Result = LinkTables("C:\fis\fis_datav3.accdb")
  37. Debug.Print Result
  38. End Sub
  39.  
  40.  
Jan 21 '11 #2
TheSmileyCoder
2,322 Expert Mod 2GB
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.
Jan 21 '11 #3
ADezii
8,834 Expert 8TB
  1. The following Code will:
    1. Open the Microsoft Office File Dialog filtered for Access Databases only (*.mdb).
    2. Allow you to select a 'single' Access Database.
    3. Loop through all the Tables in the External Database.
    4. Dynamically Link only those Tables which are valid (Non-System, Non-Temporary).
    5. Display in a Text Box exactly which Tables were Linked.
  2. Any other questions, feel free to ask.
  3. Code posted for your review.
  4. Download the Demo (Attachment) that I have created for you to get a good picture of exactly what is going on.
  5. Relevant Code:
    Expand|Select|Wrap|Line Numbers
    1. 'First, set a Reference to the Microsoft Office XX.X Object Library
    2. Dim strButtonCaption As String
    3. Dim strDialogTitle As String
    4. Dim strDBName As String
    5.  
    6. 'Define your own Captions if necessary
    7. strButtonCaption = "Open DB"
    8. strDialogTitle = "Select Database to Link"""
    9.  
    10. With Application.FileDialog(msoFileDialogFilePicker)
    11.   With .Filters
    12.     .Clear
    13.     .Add "Access Databases", "*.mdb"     'Allow Access Databases only
    14.   End With
    15.   'The Show Method returns True if 1 or more files are selected
    16.     .AllowMultiSelect = False       'Critical Line
    17.     .ButtonName = strButtonCaption
    18.     .InitialFileName = vbNullString
    19.     .InitialView = msoFileDialogViewDetails     'Detailed View
    20.     .Title = strDialogTitle
    21.   If .Show Then
    22.       strDBName = .SelectedItems(1)
    23.   End If
    24. End With
    25.  
    26. If strDBName = "" Then Exit Sub         'No DB selected
    27.  
    28. 'The External Database name is now stored in the variable strDBName
    29. Dim wrkJet As DAO.Workspace
    30. Dim dbLink As DAO.Database
    31. Dim tdf As DAO.TableDef
    32. Dim strBuild As String
    33.  
    34. Me![txtResults] = ""        'Clear Results Box
    35.  
    36. 'Create Microsoft Jet Workspace object.
    37. Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
    38.  
    39. 'Open Database Object for Shared Use.
    40. Set dbLink = wrkJet.OpenDatabase(strDBName, False)
    41.  
    42. 'Loop thru all Tables in the External Database, but ignore System
    43. 'Tables and Temporary Tables (MSys*, USys*, ~*)
    44. For Each tdf In dbLink.TableDefs
    45.   If Mid$(tdf.Name, 2, 3) <> "Sys" And Left$(tdf.Name, 1) <> "~" Then
    46.     'If you get here, Link to the Table
    47.     DoCmd.TransferDatabase acLink, "Microsoft Access", strDBName, acTable, tdf.Name, tdf.Name, False
    48.       strBuild = strBuild & "Table " & tdf.Name & " in " & strDBName & " successfully Linked!" & vbCrLf
    49.   End If
    50. Next
    51.  
    52. If strBuild = "" Then Exit Sub       'No Tables other than System and/or Temporary
    53.  
    54. Me![txtResults] = Left$(strBuild, Len(strBuild) - 2)    'Strip vbCrLf & post Results
    55.  
    56. dbLink.Close
    57. Set dbLink = Nothing
Attached Files
File Type: zip Link to External Dababase.zip (14.7 KB, 79 views)
Jan 24 '11 #4
neelsfer
547 512MB
Thx mr Adezii for the trouble
I seem to have an Access 2007 issue here. I will attach the screen pics.
Attached Files
File Type: zip filelinkerror2.zip (69.8 KB, 67 views)
Jan 25 '11 #5
neelsfer
547 512MB
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?
Jun 9 '11 #6

Sign in to post your reply or Sign up for a free account.

Similar topics

1
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...
7
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...
2
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...
1
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...
20
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...
33
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...
3
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...
1
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...
6
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...
2
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);...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
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...
0
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...
1
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)...
1
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....
0
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
0
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...
0
isladogs
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...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.