Connection to an ODBC database with a macro

[gcc]

I'm trying to use either a macro or code to perform the following:

-go to file menu
-choose get external data
-choose import
-choose ODBC database from the drop down menu
-choose the machine code tab
-choose a specific option in the list
-enter the users username

I tried writing a macro, but it only brought me as far as the import dialog box.

Any suggestions would be appreciated.

Sincerely,

John Canon
Computer Services
Greenfield Community College www.gcc.mass.edu

 

[cstaab]

Here is a little piece of code that I used to connect automatically within a macro. You can play around with it and see if you can custom fit it to fit your needs.

ConnectString = "ODBC;DBALIAS=DBP01MUF;UID=" & UserID & ";PWD=" & Pwd & _
";DSN=DBP01MUF"

Set wrkODBC = CreateWorkspace("NewODBCWorkspace", "admin", "", dbUseODBC)

Set conOOQuery1 = wrkODBC.OpenConnection("DBP01MUF", dbDriverNoPrompt, False, _
ConnectString)

Application.StatusBar = "Connected... Processing Lines"

' Set QueryTimeout to 0 for NO timeout
conOOQuery1.QueryTimeout = 0

SQLString = "SELECT PO_LOT.CD_DPT," & _
"Sum(PO_LOT.AMT_PO_LOO_RTL)," & _
"Sum(PO_LOT.AMT_PO_LOO_CST) " & _
"FROM PROD.PO_LOT PO_LOT " & _
"WHERE (PO_LOT.DT_ETA>=" & StartDate2 & " AND " & _
"PO_LOT.DT_ETA<=" & StopDate2 & ") AND " & _
"(PO_LOT.IND_PO_STS>=02 AND " & _
"PO_LOT.IND_PO_STS<=09) OR " & _
"(PO_LOT.DT_ETA<=0129) AND " & _
"(PO_LOT.IND_PO_STS>=02 AND " & _
"PO_LOT.IND_PO_STS<=09) " & _
"GROUP BY PO_LOT.CD_DPT"

Set rsOOQuery1 = conOOQuery1.OpenRecordset(SQLString, dbOpenForwardOnly, 0)

WriteRecordSet rsOOQuery1

Application.StatusBar = "Processing Complete"
conOOQuery1.Close
wrkODBC.Close

Let me know if this works for you.

Cody

 

[cstaab]

I just realized that I switched forums and I gave you the code that I have for an Excel Spreadsheet. It may work but I'm not sure. Sorry for the confusion.