September 13, 2012 at 8:05 am
I used the Access upsize wizard to split my database. I still wish to use the Access for the frontend and I don't want to have to go to all the computers to setup the ODBC connection.
I have been trying to add some code to the autoexec macro/module in access and the connection fails.
Here is one of my tries, but I have done it a gazillion other ways also without success.
oConn.Open "ODBC;Driver={SQL Server};" & _
"Server=xxxxMSQL01;" & _
"Database=sublist;" & _
"Trusted_Connection=Yes"
Can anyone help?
Thanks. [Smile]
September 13, 2012 at 10:09 am
jane.frasier (9/13/2012)
I used the Access upsize wizard to split my database. I still wish to use the Access for the frontend and I don't want to have to go to all the computers to setup the ODBC connection.I have been trying to add some code to the autoexec macro/module in access and the connection fails.
Here is one of my tries, but I have done it a gazillion other ways also without success.
oConn.Open "ODBC;Driver={SQL Server};" & _
"Server=xxxxMSQL01;" & _
"Database=sublist;" & _
"Trusted_Connection=Yes"
Can anyone help?
Thanks. [Smile]
Not sure what you are trying to do here exactly. Are you trying to use oConn which is an ODBC connection? Can you just change the type of connection and use ADODB instead?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 13, 2012 at 12:42 pm
This is the first time that I have done this. I guess since on MY computer I setup an ODBC DSN and that worked, that for people using this database on other computers I thought it would need to be ODBC. If I need to do it another way, what do you suggest?
I am using Access as the application frontend to a SQL database and am just trying to make a connection between them.
Thanks.
Jane
September 13, 2012 at 12:53 pm
jane.frasier (9/13/2012)
This is the first time that I have done this. I guess since on MY computer I setup an ODBC DSN and that worked, that for people using this database on other computers I thought it would need to be ODBC. If I need to do it another way, what do you suggest?I am using Access as the application frontend to a SQL database and am just trying to make a connection between them.
Thanks.
Jane
I would use ADODB connection instead. You have to make some adjustments but it is far more flexible than using DSN. Of course you have already discovered how painful that is to change the DSN on every client. 😉
http://www.w3schools.com/ado/ado_ref_connection.asp
Then you can check out http://www.connectionstrings.com/[/url] to get the correct syntax for you connection string.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 13, 2012 at 5:03 pm
I figured it out. When I first used the Access Upsize Wizard it prompted me for a DSN so I chose it from the Data Sources (ODBC) from the control panel on my computer. The link to the table then was (dbo.tSublistc DSN=sublist). I created a very small database that I upsized with the wizard and the link just gave (Table1 DATABASE=testconnection)
I redid my sublist database and now it is working fine.
Thanks to all who read and replied to this post.
Jane 😉
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply