ODBC Question

[jalenben1]

I am trying to automate my database using MS Access. I have created a form in MS Access. I have also created an query using ODBC which imports to a spreadsheet. The name of the query is called Huawei Tickets Query.dgy. The ODBC query is an extension file called (.dgy). What I would like to do is create command button in my form that would bring in the data into the form I created. The VBA script I have so far is as follows:

Private Sub REFRESH_DATA_Click()
On Error GoTo Err_REFRESH_DATA_Click


DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70

Exit_REFRESH_DATA_Click:
Exit Sub

Err_REFRESH_DATA_Click:
MsgBox Err.Description
Resume Exit_REFRESH_DATA_Click

I am not sure how to script it to where my ODBC query can bring the data into my form. Any suggestions??

 

[dhookom]

It isn't clear where you data actually resides. I think it might be in Excel. Typically you simply just link to the external data so that it can appear in forms and reports.

Duane
Hook'D on Access
MS Access MVP

 

[jalenben1]

The data is from our database REMEDY. I am able to generate my report using ODBC which comes through to an Excel spreadsheet. I also have a main spreadsheet using the generated spreadsheet via ODBC but in my main data source I have formulas. I need to write a script that will pull that data into the form. Again the ODBC query I generated in in an extension file(.dgy)

 

[dhookom]

Why can't you use ODBC to connect directly to REMEDY? You can use formulas and expressions in Access.

If you can't connect directly, apparently your data is in Excel where you can create a linked table.

Duane
Hook'D on Access
MS Access MVP

 

[jalenben1]

For some reason I cannot import the REMEDY tables into Access. However I am able to get to the tables via ODBC via Excel. I was able to create my ODBC query and save it on my desktop and all I do is go to Excel go to the ODBC query file and click on the ODBC query on my desktop and the data gets generatted. Since that is the case I wanted to have the ODBC generated data imported into the form I have created in MS Access.

 

[traingamer]

If you can connect using Excel via ODBC, you should be able to connect using Access via ODBC.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[jalenben1]

I am able to get to the tables via ODBC via Excel. I was able to create my ODBC query and save it on my desktop and all I do is go to Excel go to the ODBC query file and click on the ODBC query on my desktop and the data gets generatted. Since that is the case what steps do I need to take to have my data generate in the form I created in Access?