 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Multiuser / Networking / May 2007connecting to backend database
I'm trying to setup a fe/be database using a system dsn. I can't use mysel or sql for the backend and I don't know what the filepath will be for the be so I need to use system dsn so I can specify the path the same on each of the network computers. However I can't find anywhere any instruction on how to set this up. All the coding samples and suggestions use Provider=microsoft.jet.oledb.4.0 and specify a filepath name. I tried using MSDASQL and DSN name and all I get is parameters missing error messages. I've posted below the various attempts (all wrong). I have it set up with a local solution whilst I build the rest of the app, however getting the connection right is becoming a real bugbear. Please help Sub opendbconnection() On Error GoTo Handleerror 'gstrconnection = "Provider = MSDASQL;" & _ ' "ODBC;DSN=compcontrol;" 'gstrconnection = "Provider = microsoft.jet.oledb.4.0;" & _ ' "ODBC;DSN=compcontrol;" gstrconnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & "\compcontrol.mdb;" 'create a new connection instance and open it using the connection string Set gcnconn = New ADODB.Connection 'With gcnconn ' .Provider = "Microsoft.Jet.OLEDB.4.0;" '.Provider = "MSDASQL" '.Properties("Data Source").value = gstrconnection '.Open 'End With gcnconn.Open gstrconnection Exit Sub Handleerror: generalerrorhandler Err.Number, Err.Description, "modstartup", "opendbconnection" Exit Sub End Sub > I'm trying to setup a fe/be database using a system dsn. I can't use mysel or > sql for the backend and I don't know what the filepath will be for the be so [quoted text clipped - 43 lines] > Exit Sub > End Sub One, what version of Access are you running for the FE and BE? >> I'm trying to setup a fe/be database using a system dsn. I can't use mysel or >> sql for the backend and I don't know what the filepath will be for the be so [quoted text clipped - 3 lines] > >One, what version of Access are you running for the FE and BE? access 2003. DSN are only for ODBC providers, not OLEDB. More info: http://www.asp101.com/articles/john/connstring/default.asp http://www.connectionstrings.com/?carrier=access http://www.carlprothman.net/Technology/ConnectionStrings/ODBCDSN/tabid/89/Defaul t.aspx  SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) > I'm trying to setup a fe/be database using a system dsn. I can't use mysel > or [quoted text clipped - 49 lines] > Exit Sub > End Sub On May 11, 3:31 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote: > DSN are only for ODBC providers, not OLEDB. More info: > [quoted text clipped - 60 lines] > > - Show quoted text - Keep it simple also, see Joan's post about Linked Table Manager. Access will do all the work for you. > On May 11, 3:31 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the > blanks, no spam please)> wrote: [quoted text clipped - 68 lines] > > - Show quoted text - Sorry left out the link http://groups.google.com/group/microsoft.public.access.multiuser/browse_thread/t hread/59dc89da861acb7d/36a13645d166a84b?hl=en#36a13645d166a84b >On May 11, 3:31 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the >blanks, no spam please)> wrote: [quoted text clipped - 6 lines] >Keep it simple also, see Joan's post about Linked Table Manager. >Access will do all the work for you. linked table manager isn't an option having to do everything through ADOB recordsets. >DSN are only for ODBC providers, not OLEDB. More info: > [quoted text clipped - 7 lines] >> Exit Sub >> End Sub If you look at the comment marks you can see in the code I posted earlier I tried the ideas shown in the threads and they didn't work Yes, I've saw that you have tried a lot of thing; however, many of these have at least one error and for the others, it's far from clear if you are trying to construct a FE/BE based on linked tables or if you are trying to create your own recordsets using ADO objects. Seeing things like « .Properties("Data Source").value = gstrconnection » is also surprising and confusing. Secondo, the string "ODBC;" is not part of a connection string. When you are creating an ODBC linked table in Access/JET; you add the "ODBC;" at the *beginning* of the connection string to make a distinction between ODBC and ODBCDirect workspaces but this is only when creating a linked table under JET and it must be at the beginning of the connection string. DSN are only for ODBC drivers, so you cannot use this with the JET OLEDB provider or most of other OLEDB providers. The only exception to this is the strange Microsoft OLEDB Provider for ODBC (MSDASQL) which is a chimira half OLEDB, half ODBC. Also, when using a DSN, the only parameters that you can add are the UID and the PWD. Using a connection string is easy when you don't confuse "ODBC;", ODBC providers, DSN and OLEDB providers all in the same soup. SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) >>DSN are only for ODBC providers, not OLEDB. More info: >> [quoted text clipped - 12 lines] > I > tried the ideas shown in the threads and they didn't work >Yes, I've saw that you have tried a lot of thing; however, many of these >have at least one error and for the others, it's far from clear if you are [quoted text clipped - 21 lines] > >>>DSN are only for ODBC providers, not OLEDB. More info: Thanks Sylvia for that. Ok the only tables in the fe will be lookup tables which aren't going to change much with a module to check the current status of the equivalents stored in the be. There will be no linked tables at all. All the queries are contained within modules rather than being done seperately. The errors and confusion has come form trying code from different books, sites and sort of shooting in the dark to try and get this to work as I haven't found one yet that explains this part of access vba very well and I have a fair few. if you have any suggestions on books to increase my knowledge on this area that would be appreciated. I have all the wrox vba for 2003 as well as microsoft press's books for access as well as goodness knows how many others. back to the system. Basically the system dsn is called as compcontrol. all the record handling will be done through adodb recordsets. Hey Sylvain, Please could you put the correct string up for me as still trying to get it to work and you eluded that each one was slightly wrong. Thanks Jeremy >Yes, I've saw that you have tried a lot of thing; however, many of these >have at least one error and for the others, it's far from clear if you are [quoted text clipped - 25 lines] >> I >> tried the ideas shown in the threads and they didn't work If I understand clearly your first post, your code work when you are not using a DSN and it doesn't work when you are trying to use a DSN? First, you must make sure of using a System DSN, not a User or a file DSN (harder to use because of permission problems). Second, beside the eventual use of Uid and Pwd when connecting to a SQL-Server backend, your connection string must only have the DSN parameter. As it is a DSN, you must use the ODBC driver or the special MSDASQL OLEDB driver and nothing else (for example, no JET OLEDB provider). If you want to use or have a MDW file for security purpose, then this must be set as part of the DSN itself and not in the connection string. It's also best to set an eventual ID and password to be used for the MDW file in the DSN itself. For a password protected MDB file, I don't know. If your backend database is always located on the same server at the same place everywhere, why don't you keep using a DSN-less connection for your code? SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) > Hey Sylvain, > [quoted text clipped - 38 lines] >>> I >>> tried the ideas shown in the threads and they didn't work Thanks sylvia, The very first connection I wrote was gstrconnection="Provider:MSDASQL;DSN=compcontrol;" open gconn.open gstrconnection unfortunately this brought back the error message saying that parameters were missing number expected 2 that's when i started playing with all other combinations trying to get it to work I followed the instructions of setting up a system dsn(same in every book i have) so that seems right. I'm going to set up the password and user id within the system later on and thereby keep the main userid and password simple. >If I understand clearly your first post, your code work when you are not >using a DSN and it doesn't work when you are trying to use a DSN? [quoted text clipped - 23 lines] >>>> I >>>> tried the ideas shown in the threads and they didn't work Here a full example: Dim cnn As ADODB.Connection Dim rs As ADODB.Recordset Set cnn = New ADODB.Connection cnn.Open ("DSN=db2;") Set rs = cnn.Execute("select * from table1", dbOpenDynaset) If Not (rs.EOF) Then MsgBox rs("s") End If rs.Close Set rs = Nothing cnn.Close Set cnn = Nothing In the case of UID and password, you can also provide them as supplemental parameters to the call to cnn.Open (...) SignatureSylvain Lafontaine, ing. MVP - Technologies Virtual-PC E-mail: sylvain aei ca (fill the blanks, no spam please) > Thanks sylvia, > [quoted text clipped - 49 lines] >>>>> I >>>>> tried the ideas shown in the threads and they didn't work |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.