Request to add form and button to run coding
Hi Expert,
I need help to create a form on attached Access file, when i click a button it should select the "test.dsn" ODBS by default which is saved in C:\Document\test.dsn and run coding which is on "Query1 _Top100" and that table to exported into Excel on the same path where Access file saved,
I am also ok with VBA for complete coding
Please advise is that possible!
And also please advise is that possible to create MS Access table as ODBS data base for testing purpose to run a query
Thanks!
Database2.accdb
I need help to create a form on attached Access file, when i click a button it should select the "test.dsn" ODBS by default which is saved in C:\Document\test.dsn and run coding which is on "Query1 _Top100" and that table to exported into Excel on the same path where Access file saved,
I am also ok with VBA for complete coding
Please advise is that possible!
And also please advise is that possible to create MS Access table as ODBS data base for testing purpose to run a query
Thanks!
Database2.accdb
ASKER
Hi
I am just asking for testing purposes to convert access into ODBC, I need a form and button to run a code and auto select ODBC
Instead of asking each time for me to select
I am just asking for testing purposes to convert access into ODBC, I need a form and button to run a code and auto select ODBC
Instead of asking each time for me to select
You didn't clarify so I'll try again.
Access is a Rapid Application Development (RAD) tool. It is NOT a database engine. Access is used to develop applications. The two desktop database engines frequently confused with Access are Jet (.mdb) and ACE (.accdb). If you are developing the form using Access, you would NOT use ODBC to link to Jet or ACE tables.
WHAT is your Front End (the forms, reports, code) being developed with?
WHAT is your Back End (the database engine that manages the data)?
Access is a Rapid Application Development (RAD) tool. It is NOT a database engine. Access is used to develop applications. The two desktop database engines frequently confused with Access are Jet (.mdb) and ACE (.accdb). If you are developing the form using Access, you would NOT use ODBC to link to Jet or ACE tables.
WHAT is your Front End (the forms, reports, code) being developed with?
WHAT is your Back End (the database engine that manages the data)?
ASKER
Hi,
My Front end application is MS Access and back end ODBC/SQL data, i have created from and code running sucessfully,
My only concern now is each and every time i am browsing and selection "test.dsn" manually, i need to refer this path and run query with out asking for selection
"C:\Users\ADMIN\Documents\
is that possible ?
My Front end application is MS Access and back end ODBC/SQL data, i have created from and code running sucessfully,
My only concern now is each and every time i am browsing and selection "test.dsn" manually, i need to refer this path and run query with out asking for selection
"C:\Users\ADMIN\Documents\
is that possible ?
This coding i am using to run Query1
Private Sub Command3_Click()
Me.RecordSource = "Query1"
End Sub
Instead of creating a DSN and referring to it everytime you can also hard code in the ODBC Connection String (Property Sheet of Passthrough Queyry)
Like that :
ODBC;DRIVER=The Name of the Driver;SERVER=YourServer;Database=TheNameofThedatabase;User=Username;Password=YourPassword
the above is one line...i put in code to make it clearer
Like that :
ODBC;DRIVER=The Name of the Driver;SERVER=YourServer;Database=TheNameofThedatabase;User=Username;Password=YourPassword
the above is one line...i put in code to make it clearer
ODBC;DRIVER=The Name of the Driver;SERVER=YourServer;Database=TheNameofThedatabase;User=Username;Password=YourPasswordASKER
Thanks JT,
is that any other option available by directly select "C:\Users\ADMIN\Documents\
if not i can try the above coding today
is that any other option available by directly select "C:\Users\ADMIN\Documents\
if not i can try the above coding today
You can try selecting via FileDialog but i don't think that it will suit you..you can pick the DSN...read the values and modify the Connection Str
ASKER
I have tried it won't work
Probably something you missed...i took it from my own application...examine carefully each of the bold fields
ASKER
Hi JT,
Here is the details, if i run this SQL code should i get output as i expected
Here is the details, if i run this SQL code should i get output as i expected
SELECT * FROM [FabDesign].[dbo].[Schedule];
ODBC;DRIVER=SQL Server;SERVER=DATA\ULTRA;Database=fabdesigndtc;ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi
I am getting error while running code
I am getting error while running code
You have error in the connection String the server name.
ASKER
Thanks jhon
Is there some reason you don't want to link the tables permanently? You should not have to provide a connection every time you run a query.
ASKER
Hi pathartman,
I know excel vba, don't have any knowledge in Access VBA,
if that would be possible and easy to understand please provide an suggestions for the same
I know excel vba, don't have any knowledge in Access VBA,
if that would be possible and easy to understand please provide an suggestions for the same
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Expert's,
Is there a way to modify the code to use Windows Authentication and not SQL auth to login or connect
Is there a way to modify the code to use Windows Authentication and not SQL auth to login or connect
ASKER
Hi PatHartman
Will check and get back to you, many thanks
Will check and get back to you, many thanks
ASKER
Hi Expert's,
Is there a way to modify the code to use Windows Authentication and not SQL auth to login or connect
Is there a way to modify the code to use Windows Authentication and not SQL auth to login or connect
You would need to use the ODBC tool and modify the DSN since the type of logon is specified there.
ASKER
Many thanks to John and Pat for your timely advise, i have completed successfully with help of you
You would use TransferSpreadsheet to export to Excel.