More Access than Excel but you're a clever lot....

[DonkeyOte]

Anyone able to tell me how I can get my Access application to run a Query into my accounts system (set up via ODBC) - and return the values to a table?

i.e. what is my code to create the ODBC connection?

Much obliged and all that!

Luke

 

[Ian Mac]

On 2002-09-12 03:13, lasw10 wrote:
Anyone able to tell me how I can get my Access application to run a Query into my accounts system (set up via ODBC) - and return the values to a table?

i.e. what is my code to create the ODBC connection?

Much obliged and all that!

Luke

Is your accounts system Excel??
Have you looked MSQuery 'Get External Data' in Excel?

 

[DonkeyOte]

Thanks Ian but I am looking at this very differently.

My accounts system is Sage which is queried via ODBC (DSN). Also, I am not querying from Excel but Access.
Most important is that I am not running anything through MS Query as I want this to be automated within a module/macro/query in Access. At the moment I am querying like that into an Excel spreadsheet.

I guess I am after the connection syntax to an ODBC driver for Access (using DSN's.)

God, this sounds long winded but basically...

WHAT I WANT TO DO...

I want to store the data from the main table in my accounts system in Access, however, I do not want to store ALL the data from the Sage main table - I want to query the Sage Main Table (using SQL in Access) so that I only retrieve a set amount of data. So I need to know how to connect to Sage in code.

I am then querying this new table in Access from Excel.

Make any sense at all?

 

[Ian Mac]

Really an Access question?

I'm sure of the full code you'd need but assuming that this syntax works with Access as it does with Excel:

Private Sub ODBCDirectTest()
Dim wrkKros As Workspace
Dim cnnKros As Connection
Dim qdfKros As QueryDef
Dim Results As Recordset
Dim Ret As Boolean
Dim sqlKros As String

Set wrkKros = CreateWorkspace("mykronos", "admin", "", dbUseODBC)
Set cnnKros = wrkKros.OpenConnection("", dbDriverNoPrompt, False, "ODBC;dsn=kronos;uid=userid;pwd=Password;")

Ret = wrkKros.Connections.Count

If Ret = False Then
MsgBox ("There was an Error connecting to the database"), vbCritical, "Error"
End
End If

sqlKros = "SELECT VWACTIVEEMPS.EMPLOYEEID, VWACTIVEEMPS.FIRSTNAME, VWACTIVEEMPS.MIDDLEINITIAL, VWACTIVEEMPS.LASTNAME, VWACTIVEEMPS.FULLNAME, VWACTIVEEMPS.EMPLOYEENUMBER, VWACTIVEEMPS.HIREDATE, VWACTIVEEMPS.WAGER" & _
"ATE, VWACTIVEEMPS.ACCRUALPROFILE, VWACTIVEEMPS.SITE, VWACTIVEEMPS.DEPT, VWACTIVEEMPS.JOBCODE, VWACTIVEEMPS.ACTIVITYCODE, VWACTIVEEMPS.LINEMANAGER, VWACTIVEEMPS.EMPLOYEETYPE, VWACTIVEEMPS.TEMP" & Chr(13) & "" & Chr(10) & "FROM TK" & _
"CSOWNER.VWACTIVEEMPS VWACTIVEEMPS"
Set qdfKros = cnnKros.CreateQueryDef("kronostemp", sqlKros)

Presuming you have the data source name set up it will pull the info into Excel.

Sorry it's not a full answer but it should give a sniff of the correct syntax.

 

[DonkeyOte]

Thanks a lot Ian - that's a great help - I'll get cracking on it this afternoon...