Query for temp linked table

[compooper]

Hi folks,

How do you create a query that will run on a temporary table?
And then how do you ask through vba to run the query?

If you look at my code below:


I am essentially temporary linking a file, & I want to extract certain fields from that table.
But before I extract, I need to delete and unneccessary information & repeat some data in order to have no blank fields.
Later, I'll be cleaning the repeated information.

Function LinkExternal(ByVal conString As String, sourceTable As String)
Dim db As Database
Dim i As Integer
Dim j As Integer
Dim linktbldef As TableDef
Dim rst As Recordset
Set db = CurrentDb
Set linktbldef = db.CreateTableDef("tmptable") 'create temporary table definition
linktbldef.Connect = conString 'set the connection string
' run query: delete all blank records in tmptable
' run query: delete all unimportant fields
' run query: append/repeat clientname for their respective order
' run query: add field companyname, get data from form
linktbldef.SourceTableName = sourceTable 'attach the source table
db.TableDefs.Append linktbldef 'add the table definition to the group
db.TableDefs.Refresh 'refresh the tabledefinitions
linktbldef.Name = sourceTable 'rename the tmptable to original source table name

db.TableDefs.Delete sourceTable 'remove to keep the table linked
db.Close
Set linktbldef = Nothing
Set db = Nothing
End Function

[compooper]

So I tried adding DoCmd.RunSQL statement, but I'm getting an error:
Syntax error in query expression, ExamTest.*, ExamTest.Fee

Function LinkExternal(ByVal conString As String, sourceTable As String)
Dim db As Database
Dim i As Integer
Dim j As Integer
Dim linktbldef As TableDef
Dim rst As Recordset
Set db = CurrentDb
Set linktbldef = db.CreateTableDef("ExamTest") 'create temporary table definition
linktbldef.Connect = conString 'set the connection string
' run query: delete all blank records in tmptable
DoCmd.RunSQL "DELETE ExamTest.* ExamTest.Fee FROM ExamTest WHERE ((ExamTest.Fee) Is Null)"
' run query: delete all unimportant fields
' run query: append/repeat clientname for their respective order
' run query: add field companyname, get data from form
linktbldef.SourceTableName = sourceTable 'attach the source table
db.TableDefs.Append linktbldef 'add the table definition to the group
db.TableDefs.Refresh 'refresh the tabledefinitions
linktbldef.Name = sourceTable 'rename the tmptable to original source table name

db.TableDefs.Delete sourceTable 'remove to keep the table linked
db.Close
Set linktbldef = Nothing
Set db = Nothing
End Function

[orange]

A couple of things:

That is not the correct syntax for an SQL Delete query, but more importantly -- What is it you are really trying to accomplish?
Perhaps there are other options.

If you have records to link to, why not clean up those records first. You don't have to delete unnecessary fields, you just ignore them-- unless there's more to your situation.

[compooper]

Hi Orange,

Thanks for getting back to me.
I may be going the wrong direction in deleting the Null records, but here's the idea.

If you look at the table below, this is the general format of the linked-table.

The first row has a company header that I am going to ignore/delete.
I only need the following fields:
Policy #/DOB
Applican/Notes
Poicy #/DOB
Svc
Description
Fee
Case #/Date
Completed

Which will be appended to my existing table under the following fields:
App_policynum
App_lname
App_fname
App_dob
Svc_name
Svc_fee
Date_rcvd
Date_completed

As you can see there are many issues here:
1) Some fields in the linked-table contain two pieces of information
2) For every Client, the name appears once and all of the services completed on their behalf
3) The actual end of the file variates

I need a way to extract this information and add it to my existing table.
And add a few more fields:
CompanyID
InvTypeID
InvPeriod
That way I can differientiate by company, invoice type, and period.
(There are many more companies that send us invoices as well, with different formats that I'll be generating a code to deal with)

Please let me know if you have any ideas

[orange]

I just glanced at your post, and have to go out soon.
My first thoughts are:
- create a routine to format the various formats you're getting.
- include the company, date etc in the routine

Don't try to handle every such format in logic in your production process.

Format them, then use that output into your Process.
You could separate invalids by Company and Date etc long with an error message/.format issue. I don't know how many companies send these various formats, but I'd work on getting them into a standard format.

Just my 2 cents...

[compooper]

Unfortunately, the companies are not willing to change their formats. So I have to deal with this.

Could you suggest a code that runs sql queries on a temporary table and appends them to an existing table?

Please and thank you

[orange]

Unfortunately, the companies are not willing to change their formats. So I have to deal with this.

Could you suggest a code that runs sql queries on a temporary table and appends them to an existing table?

Please and thank you

I think you misunderstood what I was saying.

Suppose you have 3 companies and each has its own format for the data. What you need is a formatter that can take some parameters and convert Company 1 data and put it in the format you need.
So the data from company 1, say C1 data ==> Desired format, and
Company2 data, say C2 data :::> desired format etc.
All I was saying is a format routine to take a company's data and transform it to your desired format.

Perhaps if you had some sample data form Company X and you could tell us what the "proper format (YOUR DESIRED FORMAT) " should be.


Then your process becomes

Take company X data thru formatter for X to get desired format (YourFormat)
Take company Y data thru formatter for Y to get desired format (YourFormat)

Then use the data in desired format (YourFormat) into your main process/procedure.

[compooper]

Hi Orange,

Yes I think I did misunderstand.

When you mean formatter, is this manual or by code?
Can I run the formatter once I link the file?
Or does the file have to be formatted before I link it to Access?

I still want to keep a version of the original data, so I was hoping I could just extract the fields I needed once I linked them.

[compooper]

In case this is what you are asking for.
This is how I want the data to be in my merged table.