Access2000: Pass-through query from Excel table

[Arvi Laanemets]

Hi

Can someone give me a hint, how to create a subject! I did something alike
(but the data source wasn't an Excel table) a year or two ago, but all
materials I did use then were lost after HD on my computed died away.

I have some Excel file (p.e. MyExcelFile), with a couple of named ranges
(p.e. MyRange) defined. In same catalog, I have a Access project with table
MyTable. The structure of MyTable is identical to excel table, defined as
MyRange. My task is to add to MyTable all records from MyRange, which are
missing in MyTable.

My idea is to have a pass-through query, which retrieves all data from
MyRange. The another query will then get all records from pass-through one,
not found in MyTable, and add them into MyTable.

(I found a couple of instructions when googling, but couldn't make them
work.
The 1st is from MS KB - the query is created by procedure.
Dim MyDatabase As DAO.Database
returns an error "User-defined type not defined".
The 2nd was also from MS - how to create pass-through query from query
wizard. I haven't found a way to create a working ODBC Connect. Str.
property there.


Thanks in advance for any help

 

[Douglas J Steele]

You might have better luck asking in a newsgroup related to Excel, since
you're not actually using Access (you're trying to get data from a Jet
database)

However, to make the first method work, make sure you have a reference to
set to Microsoft DAO 3.6 Object Library. You can check your references in
Excel under Tools | References when you're in the VB Editor (and no code is
active)

 

[Arvi Laanemets]

Hi

OK. DAO 3.6 was missing really. I added it.

Now the procedure did run. But is there a way to define connection directly
in VBA. At moment I didn't see another solution, as to define the connection
in my computer as File DSN - but it means anyone who need to use this Access
project, must define this connection in his computer too.

Function CreateSPT(SPTQueryName As String, SQLString As String, _
ConnectString As String)
'-----------------------------------------------
' FUNCTION: CreateSPT()
' PURPOSE:
' Creates an SQL pass-through query using the supplied arguments:
' SPTQueryName: the name of the query to create
' SQLString: the query's SQL string
' ConnectString: the ODBC connect string, this must be at
' least "ODBC;"
'-----------------------------------------------
Dim mydatabase As DAO.Database, myquerydef As DAO.QueryDef

Set mydatabase = DBEngine.Workspaces(0).Databases(0)
Set myquerydef = mydatabase.CreateQueryDef(SPTQueryName)

myquerydef.connect = ConnectString
myquerydef.sql = SQLString
myquerydef.Close
End Function

I I did run the procedure:
?CreateSPT("Test","SELECT * FROM MyRange","ODBC;")
, and an pass-through query was created. I run the query, I select the
connection I created before - and I get an error message "You cannot use
ODBC to import from, export to, or link an external Microsoft Jet or ISAM
database table to your database"
???

 

[Douglas J Steele]

What are you using as ConnectString? It's probably possible to specify a
DSN-less ODBC connection string. Check
http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSNLess/tabid/90/Default.aspx
or http://www.connectionstrings.com/