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
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

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...
 

Forum statistics

Threads
1,172,201
Messages
5,879,650
Members
433,449
Latest member
Treavus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top