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

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

Ian Mac

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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

MrExcel MVP
Joined
Feb 20, 2002
Messages
1,174
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

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
Thanks a lot Ian - that's a great help - I'll get cracking on it this afternoon...
 

Watch MrExcel Video

Forum statistics

Threads
1,118,799
Messages
5,574,380
Members
412,589
Latest member
ArtBOM
Top