Linking An External Table Through ADO - Not Working!

Feb 17, 2005

code Fails at appending new table to catalog,
i am missing something, don'tknow what though,

thanks in advance,
sportsguy


Private Const strProvider As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="

Private Sub ADOLinkProjMaster_DblClick(Cancel As Integer)

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

cnn.Open strProvider & "F:PROJMASTER.mdb"
Debug.Print cnn.ConnectionString

Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog

Dim tblLink As ADOX.Table
Set tblLink = New ADOX.Table

' Open the catalog.
cat.ActiveConnection = cnn


With tblLink
' Name the new Table and set its ParentCatalog property to the
' open Catalog to allow access to the Properties collection.
.Name = "PROJMSTR"
Set .ParentCatalog = cat
' Set the properties to create the link.
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Datasource") = strProvider & "F:PROJMASTER.mdb"
.Properties("Jet OLEDB:Remote Table Name") = "PROJMASTER"
End With

' Append the table to the Tables collection.
cat.Tables.Append tblLink


Set cat = Nothing

End Sub


what am i missing??

oh, and i shouldn't see the table in the db container either, correct?

View Replies


ADVERTISEMENT

Modules & VBA :: Linking External Tables To Current Database?

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

Linked (external) Table Help

May 29, 2005

I have a database which contains a link to an external table.
This link is broken, however, if the external table's database is moved (yes, I know this isn't a suprise).

What I want to know is if there's a way that would be easy for a user who's not very confident to easily change the path to the table. I don't care how--a popup box, a form, whatever, as long as it'll be easier for them than unhiding the main dtabase window, dleting the exiting link, and making a new one...

Is this possible?

View 2 Replies View Related

Err Msg : External Table Is Not In The Expected Format.

Feb 4, 2007

A lady at work created an Access database that imports a dBase 5 file and when I go to import this file on User #1 PC it gives the error;

"External table is not in the expected format."

I can use the same Access database and same dBase file to import (stored on a shared network drive) from User #2 & #3's PC's and all works well. The only problem is that it is User #1 job to do this.

I unloaded Access 2003 and reloaded it and still the same results. All 3 PC's are identical via hardware and software.

It use to work until this past Tuesday when it started to give the error.

My steps are:
open the "Sales" Access database
select the prices table
Get External Data / Import and then select Prices.dbf
Then the error message comes up.

I am not an Access database guy and could use some help!

Thanks

WhooTAZ

View 2 Replies View Related

Updating Table From External Source

Sep 10, 2007

Dear Forum,

I have a question and I would be very happy if you could help me with some thoughts on how to start.

I have a database, which contains one table tbX, which ist interesting in this context. tbX needs to be updated on a regular basis, hence it would be very helpful to automate this process.

Source is the table tbY, which is located in another database in a different directory. What I am looking for is basically a query (or do I need a macro?), which deletes tbX, imports tbY and changes its name to tbX.

As there is no macro recorder like in Excel I find it very difficult to find a start here... :confused:

I'd be happy for any suggestion.

Thank you.
Diana

View 3 Replies View Related

Import Table From External Database?

Apr 23, 2013

Using access 2010. Trying to set up an import table from an external database.

Code:
DoCmd.TransferDatabase acImport, "Microsoft Access", "Y:DevTestFolder2013 01 Some Data Name", acTable, "tblName", "tblName"

I get an error 3024 could not find the file Y"DevTestFolder2013....

The database I am importing from is a 2003 .mdb

I tried the brackets because the file has spaces in it but still get the same error.

View 5 Replies View Related

Import External Table, Access Does Not Like Field Name

Sep 27, 2005

Hi,

I would like to link to an external data source (table) from MS Access 2003. I attempt to use the 'Import' feature to do this, and select 'ODBC Connection'. I am trying to connect to an application called 'Action Remedy'. I make the connection fine, but when Access attempts to import the table, it does not like one of the Action Remedy field names, and the Import aborts. The name is something like "Call Status-History.Closed.Time". Obviously, Access does not like this field name. And the chances of getting it changed (on the A/R backend are slim to none). Thus, my question: Is there away around this ?

Thanks !

View 1 Replies View Related

Tables :: Updating Table Data From External Db?

Dec 24, 2012

I have an Access db in a 3 person multi-user environment on a Windows network.

There is a "PRODUCTION" db and a "DEVELOPMENT" db.

Let's call them PROD and DEV.

PROD has the most current data, shipping records, item master, customer data, sales, etc. - but not the most current structure.

DEV has the most current struture - all the front end stuff - forms, functions, modules, etc. - but not the most current data.

The way I've handled this in the past (it seemed to work) was to take the PROD db and rename it to PRODX. Then take the DEV db and rename it PROD. Then open DEV (now called PROD) using the usual shortcut to PROD.

So now that opens fine, and I have to update all the tables from PRODX - and I mean ALL of them - since I don't really know what data has changed since the last update.If I try to delete records I get blocked by access because of all the related records (I don't have cascade delete set on every relationship). So I delete the whole table - ALL of them (this requires me to also delete the relationships). Then I IMPORT all the tables from PRODX (these have the current data). But now the relationships are all gone. It seemed at first that the relationships were back and intact - but when I last looked they were gone. So this is my problem.

HOW do I COMPLETELY empty ALL of the tables - or even delete them - and then restore them or repopulate them from an exact copy of the db but with current table data - and WITHOUT affecting the relationships?

Obviously any back end structural changes have to be handled differently. Usually by manually making the same change on the PROD db that I had in the DEV db - because deleting the table will cause the structural changes to be lost.

One last thing - I've been working this way on an un-split db, and now I'm in the process of splitting it - which SHOULD make updates much easier.

View 5 Replies View Related

Tables :: Import Table From External Database

Apr 23, 2013

Using access 2010. Trying to set up an import table from an external database.

Code:
DoCmd.TransferDatabase acImport, "Microsoft Access", "Y:DevTestFolder2013 01 Some Data Name", acTable, "tblName", "tblName"

I get an error 3024 could not find the file Y"DevTestFolder2013....

The database I am importing from is a 2003 .mdb. I tried the brackets because the file has spaces in it but still get the same error.

View 1 Replies View Related

Modules & VBA :: Find All Table Names From External File

Nov 6, 2013

I'm trying to find all the table names inside an external access file from a path.

I have a code for the user to select a file which return a patch to the file.

Then I need to find all the tables names from that file and append them to a list.

so I them can input them into a code and link the tables.

here is the code to get the file patch:

Code:
Function getFileName(path) As String
Dim f As Object
Dim varFile As Variant
Dim path
Set f = Application.FileDialog(3)

[Code] ......

View 8 Replies View Related

Appending External Text File To Table With Primary Key?

Aug 13, 2014

I have a table that contains a primary key and I am trying to append records from a text file. When I go to append the records, it tell me that XXX amount of records were lost due to key violations. Shouldn't access be able to figure out how to create new ID's on newly appended data?

Access 2007.

View 3 Replies View Related

Modules & VBA :: Get Filtered / Grouped External Data Into MS Access Table

Oct 20, 2014

I have an Excel file with a name range "DBIAS" which identifies all database data.

Then I have an Access file with a form to import that database (better, that named range) into an Access table. While importing, I have to filter some records or grouping by some field.

I cannot run correctly a VBA code to get data (filtered and/or grouped) from that name range and save those records to an existing or a brand new table.

I could get those data as DAO.recordset and printed out with "Debug.print" on immediate window, but I cannot complete the final step: writing those records to a table.

View 3 Replies View Related

Modules & VBA :: Error 3274 - External Table Is Not In Expected Format

Apr 2, 2014

I have a function that exports a number of tables within an access 2007 database to one spreadsheet using docmd.transferspreadsheet.

The error message in the title of this post presents itself during a loop which transfers the tables to the spreadsheet. However, it doesn't always happen, sometimes it completes the process perfectly which is quite frustrating.

Below is the section of code where the error occurs ....

Set rs = CurrentDb.OpenRecordset("SELECT * FROM tbl_Table_Exports WHERE Type = 'CRM'", dbOpenSnapshot)
rs.MoveFirst
Do Until rs.EOF
TableName = rs.Fields("Table")
WSName = rs.Fields("WSName")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, TableName, NewWBPath, True, WSName
rs.MoveNext
Loop

View 1 Replies View Related

Modules & VBA :: Exporting Tables - External Table Is Not In Expected Format

Sep 15, 2014

I'm currently using the following VBA to export all tables within my DB to Excel on separate tabs:

Dim td As DAO.TableDef, db As DAO.Database
Dim out_file As String
out_file = CurrentProject.Path & "" & "Backup.xls"
Set db = CurrentDb()
For Each td In db.TableDefs
If Left(td.Name, 4) = "MSys" Then
'We do not need MSys tables in excel file
Else
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
td.Name, out_file, True, Replace(td.Name, "dbo_", "") 'We do not need dbo prefix in sheetnames
End If
Next

But upon exporting I get the following error:

"Run-time error 3274' External table is not in expected format"

It then hightlights the following line:

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, _
td.Name, out_file, True, Replace(td.Name, "dbo_", "")

If i navigate to the directory i can see that it has exported it to excel(attempted), but half of the tables(tabs) are missing.

View 1 Replies View Related

Table Linking

May 19, 2005

Ok...I have an issue that I don't know how to resolve.
I have a table called 'orders' in the orders table there is a place for " quantity', 'colours' and per unit. The cost per unit is based on quantity and colours.
For example: 12-35 shirts 1 colour are $2.85 per unit. 12-35 2 colour are $3.50 and so on.
I would like to be able to put in the quantity and # of colours and the Unit price fills in a field.
I don't know how I can accomplish this.

View 2 Replies View Related

Linking Just One Table

Sep 4, 2006

I have created an Access database application that is stand alone for each account manager/client at our business except for one table that I want shared as a source of data across all separate databases that are created.

I've investigated automatic Database Splitting and this works, except I only want the one table to be split and the others remain unique to each database.

I tried exporting the table in question to another Access database and then linking the table into each other database, this seems to work except that referential integrity can't be set up as it was. Is there any way to keep the integrity between the external database and the databases it links to?

Thanks in advance for any advice.

View 1 Replies View Related

Linking A Table

Jan 20, 2005

Hoi,

I'm using 2 databases. The first is my working database (LiTiMA.mdb) the second database (LiTiPA.mdb) is just for the password table.
There is no link between the 2 databases. But when I open a form for editing the password in LiTiMA, a connection need to be created between the 2 databases.
If the form is closed the connection need to be removed.

Does someone know how to do this?

thanks
Koen

View 4 Replies View Related

Linking Paradox Table

Oct 30, 2006

I have an access dbase trying to link a paradox table. I have Office 2003 and BDE 5.01 was installed on my machine. When I try to link to a paradox table its giving me an error "Unexpected Error from xternal database driver (11265)". On the other hand, I have Office XP on my laptop and BDE 5.01 installed, I tried to link to a paradox table and it works. :eek: :confused: What can I do to link to a paradox table using Office 2003? :confused:

View 1 Replies View Related

Table Linking Query

Apr 1, 2008

Hi there,

The aim of this (as part of a bigger solution) is to create an entry system where users can enter jobs in, assign a resource to the job and give the resource a cost. The conditions are that resources can be on more than 1 job, more than 1 resource can be on a job.

Obviously entering in resources manually is not a good solutioon (eg having 20 resource fields) as it is now. I want to create a relation between tables to achieve something better.

Let's say I had (for illustration sake) 2 tables, one which is a list of people and one which is a list of jobs. I want to be able to assign a person to a job for x amount of time (defined by a start date and end date in the job table) at cost y.

So we could have a field for the people table which would relate to the job id and a field for rate in the job db. However, people can be on more than 1 job (eg after doing one they do another) and there can be multiple people on a job (having one field for rate rendering it useless)

How can this be done in the most efficient way so that any job could be looked at historically and have the relevant rates etc? Would I have to create another table with rates relating to the job ID and person ID? This would allow for multiple resources per job and to have a rate assigned for each resource.

eg if person ID = 1 and job id = 1 then fetch rate etc and display in a list, and to calculate total cost simply sum all rates where job id = 1?

Thanks

View 3 Replies View Related

Linking Datawarehouse To A Table

Nov 18, 2006

Hi guys,

I have been linking text delimited file to my Access table. The text delimited file is generated by a report run by our in-house system. I've been able to do this. Now, I want to learn how to link a datawarehouse (ODBC?) cube to Access. Is there a tutorial somewhere I can perhaps start? I tried to search this form by typing "Linking datawarehouse" I only found one post.

Thanks

View 6 Replies View Related

Table Not Linking To Query

Sep 25, 2007

I am pretty new to creating relationships between tables. I have a main form with subforms embedded. I tried running queries from the main form and they run just fine. I also run from the tables linked to the subforms just fine. however, when I try to run a query for a report that takes data from the main form and joins with data from the tables linked to the subforms all the data cells are blank. I have a linking table which is also blank. All the forms are linked by the same program ID number. Any ideas on how to resolve this? Thanks

View 14 Replies View Related

Linking Records In Same Table

Oct 10, 2007

Hi,
I have been thinking of allowing our users to be able to link records together within our database.
We have a CRM system that lists customers and their details of mortgages and other insurance related products which they have. Some entries maybe linked in some way, possibly other family members etc.

My question is, is it possible to perform this kind of action? So they can then see relationships between either related records (ID ref etc).

I'm sure I've seen this in SQL based applications, but haven't come across it within Access.

Thanks
Matt

View 2 Replies View Related

Linking An Expression With A Second Table

Jan 2, 2006

When running a query i have created an expression which is simply 2 fields joined together. JOBLOC:[JOB]&[LOCATION]. I want to link this expression with a field in a second table [SCOPE], but for the life of me can't remember how i did it. Either in query design or SQL.

Thanks for your help!

View 1 Replies View Related

Linking Table Question

Jan 30, 2007

Hi.
I have a table with Basic Client Data including their program start date.
One type of service they might, but not necessarily, get is job training. So I have a Training table.

I need a query that will show all client's names (found in Basic Client Data) with a specific start date but exclude anyone who has a specific job training service.

How to set up?

Russ

View 1 Replies View Related

Linking Form To Table?

Apr 10, 2005

Hi there,

I just added a new field to a table but i now need to add the field into a form so the table can be update. However when i open the field list to assign/bind the control the new cloumn is not present on the field list.

How do i add a new field which i have entered in the table to the field list?

Thanks Mark Flavin

View 5 Replies View Related

Form And Table Linking

May 28, 2005

Hi, i'm trying to get a field to only allow data to be entered that is in a table
e.g. i have a table which has a list of country dialling codes in it. In my form i want to only allow valid country codes to be entered-i.e. the ones which are in the table?
Ive tried coming up with different validation rules but cant seem to do it
Can anyone help?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved