Modules & VBA :: Dynamic Linking Of FoxPro Tables
Dec 11, 2013
I'm looking for some sample VBA code that dynamically creates a link to Visual FoxPro 9 table. Our group has a number of end user FoxPro applications, and as FoxPro is reaching the end of its life in January 2015, we need to replace it.
A lot of the processing we do uses tables with a date embedded in the name, e.g. MyData_20131211.dbf. We'd like to be able to let our users to use Access queries that point to these tables without having to manually create the ODBC link each day. Is there a way to set up a link once, then use VBA code to dynamically change the table it points to?
For example, we set up an ODBC link table to MyData_20131211.dbf, and rename the link table in Access to MyData_Today. Then tomorrow, the VBA code would change the link to point to MyData_20131212.dbf.
An alternative would be to dynamically recreate the link each day.
As the tables are large, we don't want to import them into Access if we don't have to.
View Replies
ADVERTISEMENT
Dec 9, 2005
hi to all,
I have a software which links itself to a fox pro database/tables. Now i want to link some of these free tables or database from fox pro to access. Whenever there is a slight change in the fox pro tables/databases they must reflect directly into access database. Is this possible and how?
thanks,
ashley
View 3 Replies
View Related
Nov 26, 2004
I have been running queries and reports with data from our company database (a third-party system based on FoxPro2.6) by linking tables into MS Access 97 for years. We have now upgraded to XP and Office 2003, and suddenly I can no longer do this but get a message "Could not find installable ISAM".
I've tried to locate the required file on the Internet but no luck so far. Can anybody help me? I am currently using an old pc with Office 97 on it to run the reports, but it's in a different office location and makes the whole job very tedious. Are there any work-arounds?
Any help much appreciated!
CC99
View 3 Replies
View Related
Apr 22, 2013
I'm trying to import a few tables from a FoxPro database into an Access Database using VBA code. I know I have done it before and I remember struggling with the format last time.
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=Hiremate;SourceDB=data-wwappshmfox32datahiremate.dbc;SourceType=DBC;E xclusive=No;BackgroundFetch=Yes;Collate=Machine;Nu ll=Yes;Deleted=Yes", acTable, "hires"
View 2 Replies
View Related
Jul 11, 2014
I am trying to concatinate string in a loop to generate a dynamic SQL to compare 2 tables. But I am not getting the result I want
Code:
Set rs = CurrentDb.OpenRecordset("r1")
Set rs2 = CurrentDb.OpenRecordset("r1_old")
columnCount = CurrentDb.TableDefs("r1").Fields.count
strSQL = ""
For I = 0 To columnCount
[Code] .....
I am trying to achieve something that reads like the following
r1.Field(0) = r2.Field(0) AND
r1.Field(1) = r2.Field(1) AND
r1.Field(2) = r2.Field(2) AND
.....
View 2 Replies
View Related
Jul 10, 2013
I have a code for linking tables. It works on Access 32 bit.I modify the code for 64 bit (include PtrSafe after all Declares, etc...)But it doesn't work.When I try to chose database in dialog window, my program closed.
Code:
Public Function GetDbPath(path_name As String) As Integer
Dim i As Long
i = MsgBox("The path to database is incorrect" _
& Chr(13) & "Chose new path?", vbOKCancel + vbExclamation)
If i <> vbOK Then
DoCmd.Quit
Exit Function
[code]...
View 3 Replies
View Related
Mar 16, 2015
I was wondering if it was possible to link random external tables to the Active Database through VBA. I would like to run the code that would open up a dialog box that would let the user select the database as well as the tables within that database that the user can select to link to. I am able to select the database and but not able to select the actual tables. The tables will be random so I can't make a constant statement for a specific database.
View 1 Replies
View Related
Aug 19, 2013
I have two forms both with separate tables
(1) Register and
(2) Payments.
One of the common denominators between them is the URN which is auto-populated as it is an auto number field. My issue is that when I want to add a new record to the payments table using the forms (I can get to the payments form via the register form), I want to be able to identify the record that I am currently viewing within the register and auto populate the URN field with the same number. This is what I have done so far,
Option Compare Database
Option Explicit
Private Sub AttachPaymentDetails()
Call PerformInsert("tblFinancialBudget", "frmFinancialBudget")
End Sub
[code]....
View 5 Replies
View Related
Dec 13, 2006
Hello everybody! i want to create a table consisting of 2 columns. one column may contain constant values and the other has to contain dynamic values (values of the second column must depend on the values of the first column). For example if the constant of the first column is ID then the variable of the second column will be ID+5. Something like that below
Column 1 Column 2
ID1 ID1+5
ID2 ID2+5
ID3 ID3+5
. .
. .
. .
Could anyone give me a tip about that? Thank you in advance...
johann
View 2 Replies
View Related
Aug 28, 2013
I want to be able to connect dynamic web data such as live gold price, exchange rates from the relevant web pages to a table in Access 2010. I have searched the forum without finding any related answers.
I understood that this can be done in Excel by using "Get External Data from Web" where the connected cells will be updated along with live data changes in the linked web page. I could set up a link table from Excel in Access but this would be tedious since the Excel file has to be opened to get the data refreshed everytime I open the Access file.
How to load dynamic data directly from web site to an Access table?
View 1 Replies
View Related
Oct 12, 2006
Okay,
First I was thinking to write a little app in Access to work with a DBF file, that I assumed was a DBIII database. It was exported via a 3rd party software, and the primary interface up until this point has been Visual FoxPro. My problem is two fold:
1st:
When I tried to import it as a dBase III file, Access just closed. I mean, disappeared, no longer running, closed. I didn't even get an error message or anything.
So I tried to import via ODBC, using a "Machine Data Source" Microsoft Visual FoxPro Database (or table, either works) and selected the directory of the dbf file and selected the table listed once it scaned the directory and all imported fine.
So: If the Visual FoxPro Machine Data source worked, though I am confused why the DBIII driver just booted me, I am now concerned as to how I would write a connection string via DAO to access that dbf file at run time.
Basically I want to go to a static location on a network and access a dbf file, and perform query manipulations upon it. My connection will be READ ONLY to the actual DBF file and i would subsequently use MS Access to create any other relevant tables/queries based on the table in the dbf file. Since the Visual FoxPro MDS diver worked, do I need to write a connection string to access it, and if so, how would I do that?
Thanks
Jaeden "Sifo Dyas" al'Raec Ruiner
View 1 Replies
View Related
Feb 6, 2007
Guys, i have a recovered Foxpro table, the problen i have is the numbers have the wrong format. To be exact, 63 should be 0.63, 1200 should be 12.00, 4250 should be 42.50. In other words i need to put in a decimal place two places to the left of each number. How do i do this on import ? Do i have to run a query and make a new table? If so what function and expression do i use?
Many Thanks:)
View 1 Replies
View Related
Dec 3, 2012
I have 2 identical database in terms of structure but it differs in data.
Basically I would like to import data from subDatabase to mainDatabase and ensuring that there are no duplicate records.
I have used the "link to datasource method" through importing the tables to have the "updating" function.
However, this method also means that the records in mainDatabase are also imported over to subDatabase which I do not want.
Is there a method to ensure that the records are shared/update one way only? (i.e. import from subDatabase to mainDatabase and not main to sub?)
View 1 Replies
View Related
Sep 26, 2012
Currently trying to build a database for customer management and order placement/tracking. Want to set a couple of rules so that if I for instance click yes of billing and shipping address the same that the database will automatically fill the shipping address with the data I inputted for the billing address in the same table.
The other issue I can see I'll run into is, I want to be able to select one of the company ID's (made up of a three letter abbreviation of the full company name) in the product ordering table and it will automatically fill in the rest of the customer data (phone, email, address etc) data into that form.
View 2 Replies
View Related
Jan 28, 2014
I have text files placed into a folder like AIN28_01012014_1355.txt
I need to import these but the files have the date and time added to the name which i cannot stop AIN28_ will always be the same is there a way to import the file just off AIN_*.txt or something like that
I was thinking i could use file copy and change the name but that needs to know the exact names doesn't it...
View 1 Replies
View Related
Jul 31, 2013
I want to know if it's possible to make a dynamic select case. In my form I have 3 separate combo boxes. What I want to happen is depending on what was selected in one the options in the other two change and if you select something in the second the option in the third narrows down again. The user can select these in any order. They can use one by itself or all three independently. In my code I can do this with many select statements, e.g
Select Case Me.combobox1.Value
Case "choice1"
Forms!CurrentForm!combobox2.RowSourceType = "Table/Query"
Forms!CurrentForm!combobox2.RowSource = "SELECT fieldname FROM tablename WHERE fieldname = '" & Me.combobox1.Value & "'"
End Select
In the place I have "choice" is it possible to write something along the lines of,Me.combobox1.value = "SQL Code"..The idea that this code would still work if the user adds more data to the tables which these combobox choices come from.
View 6 Replies
View Related
Feb 6, 2007
Guys, i have a recovered Foxpro table, the problen i have is the numbers have the wrong format. To be exact, 63 should be 0.63, 1200 should be 12.00, 4250 should be 42.50. In other words i need to put in a decimal place two places to the left of each number. How do i do this on import i? Do i have to run a query and make a new table? If so what function and expression do i use?
Many Thanks :)
View 3 Replies
View Related
Apr 15, 2014
I am doing a project where we are collecting home owner data and information on all the dogs in the household. The data for homeowner has an autonumber primary key because no field is unique or has been consistently collected. I am struggling to link the data for the dogs to the owners because an autonumber primary key will not work since not all homes have dogs. I need to have this set up so that people who are not tech savvy can look up each homeowner (or dog) and get the dog (or home owner) information. And to make things even more fun we need up to 15 potential dogs per household each of whom will have 18 different pieces of data collected.
It looks a little like this (and you can see my not matching ID issue):
Homeowner table
ID First Name Last Name...........Total Dogs
1 Max Maximus 5
2 Min Minimus 0
3 Mus Musculus 1
Dogs 1-5 table
ID Date Dog 1 Name Dog 2 Name .......Dog 5 Name
1 (Max's) 4/11/14 Bobby Billy Betty
2 (Mus's) 4/11/14 Jojo
Min will have no dog records at all, just home information.how to link the dog's to the homeowners .
View 3 Replies
View Related
Jun 30, 2006
I have a form that shows a list of all of my records in my database. I want to be able to click a button called "Report" and have that print a report that has all the records I have filtered on my form. I have a report in the format that i want it in, however, currently it prints every record and not just what is shown on my form. (The form is dynamic and I want the Report to be dynamically based on the form) HELP PLEASE!
View 6 Replies
View Related
Mar 18, 2013
I have been asked to design a database to replace our old excel sheet to log one of our customers server builds. It a four noded system and i require a seperate log for each node serial models of parts etc, qa checks.
I intitial started by just using one table but forgot the limit was 255 rows, i need about 600 - 700. So my plan is now to transfer to indivial table for each node. Then, a general table named Main for all my other details.
Although this seems simple, i now have the annoying task of being able to link them all together and produce a single PDF file to send to our customer whilst only entering our system serial number once to produce this.
My basic understanding will be to create a System Serial Number field in each table a link it via relationships, but if i produce a report with everything i need would it input my details for all four nodes? If so how would i set this up?
I will be splitting the database once completed and then making a ACCDE file for my techs to use.
View 4 Replies
View Related
May 20, 2015
I have a button that opens a report. The code behind the button builds the filter criteria for the report based on some selections in a list box.
Code:
DoCmd.openReport "Report", acViewPreview, "", GetCriteria
For VarItem = 0 To Me.List2.listcount - 1
strCriteria = strCriteria & "([ProjectNo]= '" & Me.List2.Column(1, VarItem) & "' And [ClientID] = " & Me.List2.Column(0, VarItem) & ") Or "
Next VarItem
If strCriteria <> "" Then
strCriteria = Left(strCriteria, Len(strCriteria) - 4)
Else
strCriteria = "True"
End If
GetCriteria = strCriteria
This is what the filter would look like with values after running the report (taken from filter bar in report properties):
([ProjectNo]= '150002' And [ClientID] = 206) Or ([ProjectNo]= '150003' And [ClientID] = 79)
Problem is that i only get records for ([ProjectNo]= '150002' And [ClientID] = 206). I this seems only filter ONE set of criteria ignoring all the others. What am i doing wrong?
View 5 Replies
View Related
Jun 6, 2013
Is it possible to generate a report from 2 listboxes?
I have attached a pic of what I am trying to accomplish. I am wanting to have a report open with only the criteria that a user selects from the listboxes.
listbox 1: User selects a case number(s)
listbox 2: User selects the fields they would like in the report
So, if the user selects:
Case number: 13-001
Then selects fields:
Allegation
Incident date
Focus_Last name
This report would be:
Code:
Case Number Allegation Incident Date Focus_Last Name
13-001 DUI 06/01/2013 Doe
View 5 Replies
View Related
Feb 20, 2015
I've been looking into writing an access form used for hard drive analysis and came across hex values. This made me start dabbling with numbers again and came up with the following function.If you're a person that enjoys playing with numbers, could I get you to think of any scenarios where I could solidify this function to handle errors and become as dynamic as possible!
I think the end result could be useful to add to this communities quick fix library. What it does is take two strings, value and and the definition of how that numbers counts, and with these two values it will spit out the decimal value.
examples:
HEX - "FFF", "0123456789ABCDEF"
OCT - "777", "01234567"
BINARY - "11111110", "01"
Code:
Public Function ConvertStringToDecimal(ByVal str As String, _
ByVal def As String) As Variant
Dim inc As Integer: inc = Len(def)
Dim n As Variant: n = CDec(0)
Dim i As Integer
Dim val As Integer
[code]...
View 3 Replies
View Related
Jun 25, 2014
I am trying to build a function that will create a dynamic query for a chart on a Subreport.I am not exactly sure I am going about this the right way, but I need the user to be able to change selected fields for use in the query. I have a form with 3 combobox controls for selecting options to change the SQL statement. So far my code only deals with one of these comboboxes for simplicity. There is a button to call my function. Currently, the function is setting hidden text box values based on the combo controls, but I'm not sure if this is redundant.
I am using this as my guide for building the sql, but I am having trouble picking up the values in my text boxes for use in the SQL. [URL] .....
Code:
Option Compare Database
Option Explicit
[code]...
how do I get a value from an unbound textbox on an unbound form into a string to use as sql? The value in the textbox is a number.
View 3 Replies
View Related
Jun 24, 2014
I am using John Big Booty's code for narrowing down the content of a listbox. It works beautifully, with the exception of when I type the character "i" into the search box it gives me a Runtime 2110, cannot setfocus error. I have run through the entire alphabet in lower and upper case and consistently get the code failing on lowercase i only.
Here is the code:
Private Sub searchFor_Change()
'Create a string (text) variable
Dim vSearchString As String
'Populate the string variable with the text entered in the Text Box SearchFor
vSearchString = searchFor.Text
[Code] ....
Here is the link to the original thread that the code came from. [URL] ....
View 3 Replies
View Related
Jul 27, 2015
I stumbled on to a Dynamic Multi Search form on this site and have been tailoring it to my organization's directory of contacts. Everything was going good until I was asked to include a search by groups to which each individual may belong. The data in the table is contained in Yes/No check boxes for around 30 different groups. I am hoping to add a combo box to the Dynamic Search as a means to pull up individuals in any 1 category. Below are two attempts at what I thought might work, however, neither performs any filtering.
Code:
Private Sub Groupbox_Change()
Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String
Set db = CurrentDb
Set qdf = db.QueryDefs("qryGroup")
[code]....
View 6 Replies
View Related
