Trying To Connect Automatically To SQL Server When I Open A Form
Nov 22, 2005
Hi all
I'm making an Access front-end for an existing app based (I think) on SQL Server 2000. (The Access thing makes labels... the "label wizard" supplied with the SQL Server program isn't very good.)
I can use it fine, but eventually it's going to be used by non-techie people. As it uses linked tables, when you first run it you get a message box similar to this popping up:
http://www.rainjam.com/images/login.jpg
On some machines it tries to create a trusted connection automatically, and fails, and then gives you this box with "Use Trusted Connection" ticked - thus making it even harder for people to use.
Ideally I don't want anyone to have to bother with this. Is it possible to put something into the form's OnOpen event that passes the server, database, user & password details to SQL Server, so it all happens invisibly? I've already tried creating a file DSN on the machine it'll be used on (doesn't seem to do much) and also putting this code in:
Set objConn = New ADODB.Connection
objConn.ConnectionString = "Driver={SQL Server};Server=[server];Database=[db];Uid=[blah];Pwd=[blah];"
objConn.Open
(I must admit this was a bit of a stab in the dark... it didn't work anyway)
I also found this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=50449) and tried the code but it didn't seem to work (got one of those "You entered an OnOpen expression that can't be evaluated"-type errors).
Finally... I briefly investigated redoing this whole thing as an Access Project, but haven't ever used them before and don't really have the time to learn!
Any tips would be really, really gratefully received...
Cheers
Nick
View Replies
ADVERTISEMENT
Nov 15, 2013
I have a code which connects to sql server using DAO. I would like to know is that do my system require sql server native client installed to run this code? or what software needs to be installed to have this code works for me?
View 1 Replies
View Related
Jan 28, 2007
Hi,
I am finishing an Access database for a customer that may want, in a later stage, to have a website(probably ASP) that would connect on the same database. Is that possible? I mean can an Access project on a pc connect to remote database(mdb or SQL server) used by the website?
If so can you give me some quick steps to do so or a good article on it?
Thanks.
View 6 Replies
View Related
Aug 30, 2013
When you click on your database icon to open up your database, is there a way I could set a form to automatically open ....
View 2 Replies
View Related
Aug 5, 2013
Is there a way to have a date field populate automatically when a form opens but be able to change that date if need be?
View 4 Replies
View Related
Jun 29, 2015
I have a database that has a main form, and from that main form there are buttons that open their respected forms. I am curious as to how I control the size of all of my forms. They are all pop-ups at the moment. It all works fine on my monitor, but when opened on a smaller monitor it is too big and I can't even see the bottom of the screen. The main form is set to open automatically after the user logs into the database and it opens to fit the screen. also, all of my forms are modals.
View 4 Replies
View Related
Oct 30, 2007
I am having a problem with Access 2007. I have my project split into front and back end MDBs. It use to work fine but when I upgraded to Acessw 2007 I can no longer open the project on more than one computer at a time. The first work station opens the MDB using a shortcut. After that no other computer can open it (no error message, just doesn't open) they can open Access and them open the server MDB, but thats kinda lame.
Anyone have a clue as to why the MDB can not be opened (via a shortcut) on more than one workstation?
I'm lost. . . . .
CL
View 1 Replies
View Related
Aug 6, 2006
We are struggling with the following thing. We already tried to explain it in this thread (http://www.access-programmers.co.uk/forums/showthread.php?t=111533) but we still don't get it :(.
We have a table, named "Tblapa".
This table contains: ID, UK, Area, Tf, Tt and Number
We have made a query, named "Qrychoosearea"
This query gets his input from the "Tblapa"
(and thus, the query results in: Area, UK, Tf, Tt and Number)
Now we would like to make a form, named "FrmArea" with a combobox.
This combobox lets people choose an Area of their interest out of the list of given Areas. Then they have the press an "OK" button
Upon clicking on the "OK" box we want to generate a REPORT based only on that specific Area (and the related UK, Tf, Tt and Number ofcourse) and thus not showing all Areas.
How do we have to do that?
As we are just beginners in Access and have no feeling for VBA (yet), a detailed explanation or a link to a website who explains how to do this (with images or so) would be much appreciated.
Greetz,
Math
View 10 Replies
View Related
Oct 28, 2011
I have a query, one of fields have a criteria. On other hand i have a form. User entry a value in textbox an click on a button then query run. criteria is value in textbox. What i must write in criteria?
View 1 Replies
View Related
Mar 10, 2014
I am probably staring right at it - but where is the option to set Access 2010 so that it automatically opens a specified database on startup?
View 1 Replies
View Related
Jul 31, 2013
I'm trying to tie a from into the results from and AD Query. I'm not that experienced with doing this kind of thing so I may be going about this the wrong way. Anyway here's what I've got so far:
Code:
Private Sub Form_Open(Cancel As Integer)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Set cn = New ADODB.Connection
With cn
.ConnectionString = "Provider=ADSDSOObject;Trusted_Connection=yes;"
[Code] .....
When stepping through the code, it all goes well until it hits the line that actually connects it to the form "Set Me.Recordset = rs".
When this line is executed, Access crashes and attempts to restart.
PS: Access 2010, Win7 64bit
View 5 Replies
View Related
Apr 14, 2006
what options do i have to connect to my database if back end of my db is in newyork and front end is in washington. how do i connect to my db??
View 1 Replies
View Related
Oct 29, 2007
Hello my helpfull friends,
I am going to build a multi-user database with the tables en queries stored on a server. So I will deploy multiple front ends.
Here is my question:
What is the best way to connect to the BE?
Should I link the backend using the linkingmanager (bound to a recordset) of access 2007
OR
should I interact with the data in the backend using undbound form using a disconnected recordset
OR using SQL
I would prefer the first option because it's the easiest way.
But what do you guys (and girls) recommend?
View 7 Replies
View Related
Mar 9, 2006
I tried referencing a table in my Access DB that held my login id and password info but it didn't work. How can I make it work?
ODBC;DSN=DB2Q;UID=[Table].[UserID];PWD=[Table].[Password];MODE=SHARE;DBALIAS=DB2Q;DISABLEKEYSETCURSOR=1;DIS ABLEUNICODE=1;IGNOREWARNINGS=1;
View 8 Replies
View Related
Dec 6, 2005
Hi all,
I'm designing a db in which I have a list of teacher names, and for each record (each teacher), there is a list of subject that this teacher teaches.
what i did is the following:
I have designed a form with teacher name, and a form for subjects.
I have added a button in the teacher form, next to each teacher name, this button we take the user to the subjects form to add the subjects for this particular teacher.
To link these to form, I have added the primary key of the teacher table (TeacherID) in the subjects table, so the relationship is one-to-many.
but I couldn't get to connect them.
I want the subject form to get the TeacherID from the teacher form to connect each teacher with his subjects. How can I do that?
Any help will be very much appreciated.
Best Regards,
CS.
View 3 Replies
View Related
Oct 3, 2005
Hi
I use an application called FIZZ which is a Sensory Analysis program (works with statistics etc.). This application uses files with the extension *.DB and I was wondering if it is possible to connect access to this kind of file? These file are basically tables...
Thanx for the help
View 5 Replies
View Related
Aug 20, 2006
Hi All,
I am new to ASP, so bare with me. I am trying to connect to a database with ASP thats on an online server. I am recieving the following error:
Code:Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x8fc Thread 0x1018 DBC 0x99aa314 Jet'. database/test.asp, line 15
Here is my file name "test.asp"
Code:<html><head><title>My First ASP Page</title></head><body bgcolor="white" text="black"><% 'Dimension variablesDim adoCon 'Holds the Database Connection ObjectDim rsGuestbook 'Holds the recordset for the records in the databaseDim strSQL 'Holds the SQL query to query the database 'Create an ADO connection objectSet adoCon = Server.CreateObject("ADODB.Connection") 'Set an active connection to the Connection object using a DSN-less connectionadoCon.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath( "/database/atosdba.mdb" ) & ";" 'Create an ADO recordset objectSet rsGuestbook = Server.CreateObject("ADODB.Recordset")'Initialise the strSQL variable with an SQL statement to query the databasestrSQL = "SELECT Elements_list.Sys_id, Elements_list.Element_Name FROM Elements_list; "'Open the recordset with the SQL query rsGuestbook.Open strSQL, adoCon'Loop through the recordsetDo While not rsGuestbook.EOF 'Write the HTML to display the current record in the recordset Response.Write ("<br>") Response.Write (rsGuestbook("Elements_list.Sys_id")) Response.Write ("<br>") Response.Write (rsGuestbook("Elements_list.Element_Name")) Response.Write ("<br>") 'Move to the next record in the recordset rsGuestbook.MoveNextLoop'Reset server objectsrsGuestbook.CloseSet rsGuestbook = NothingSet adoCon = Nothing%>
As i said, I am new and might be missing something simple.
Thanks for the help.
Bones
View 2 Replies
View Related
Nov 28, 2005
Hi All,
I've been working on many other domains but very new to Access world. Anyway,coming to the point.I have a database built on Access and the reporting is being done on Excel.Till now,there is no connection between both.Users are doing copy/paste procedure for reporting in excel.i tried to connect both and get the data directly in to excel with out copy/paste procedure(i.e., just by refreshing the excel sheet would do). But when i create an ODBC connection between them, it says that the required permissions are not given to you by the administrator.I'm trying to create a file DSN.so,can anyone tell me what exactly i need for this?is it the admin rights of access database (or) the admin rights of the folder where the mdb file is located?little confused on this.I already have the admin rights on the folder where the mdb file is located but still getting the error.admin rights of the datasbe cannot be given because no one knows the password of administrator ID of Access database(little funny but it is the fact!)
can anyone please let me know how can i get through this problem as this would be the crucial thing for this project.
Regards,
Ravindra
View 1 Replies
View Related
Dec 19, 2006
Hello,
Is it possible to connect to a text file via ODBC with an Access database. I have tried with a pass thrrough query but I get an error stating you can not use ODBC to link to an external Microsoft Jet or ISAM database table. I know I could import the file but this would put my database way over the 2 gig limit.
View 9 Replies
View Related
Feb 10, 2005
I was wondering if it would be possible to type in a description eg.(Days, nights, O.T.,ect.) and have excell automatically insert the rate I specify into another cell.
View 1 Replies
View Related
Nov 30, 2006
Hi
I'm new to ASP and I need to connect my webpages to an Access database I have created. I would do this in dreamweaver normally but I'm having trouble using the custom connection string function to do this. So I was hoping someone could explain how I would do this by coding it instead.
The code my web host provide to connect to a database is below. If someone could explain to me how I would use this to connect to a database, I would be very grateful. Thanks.
Quote: Dim oConn, sConnection
Set oConn = Server.CreateObject("ADODB.Connection")
sConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:sitesSingle??UserNamedatabasedata.md b;" & _
"Persist Security Info=False"
oConn.Open(sConnection)
oConn.Close
Set oConn = nothing
View 3 Replies
View Related
Oct 20, 2013
I am trying to connect to an access database format 2003 from my access 2007 database. I would like to create a recordset from a table in the 2003 database and bind it to a listbox in my 2007 database.I tried to the following code that didn't work.
Code:
Dim DNHConn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim connStr, xSource, xSys, xUsr, xPsw
On Error GoTo errH
[code]....
When I try to execute that code, I get an error that the apllication can't start because the worgroup file is either missing or opened exclusive by another user.
View 2 Replies
View Related
Dec 11, 2013
Access 2007, split database, 9 users. I have been able to link the BE tables with the Linked Table Manager. I use the DNS name path because everyone has different drive mappings. I can open my linked tables as a RecordSet and AddNew and Update records. However, I cannot Seek and Edit the linked tables. I understand that I need to open the BE tables directly in order to do this. Now, I can do a connection like this, and it works for me:
Dim dbs As DAO.Database
Set dbs = DBEngine.OpenDatabase("R:DataTask_be.accdb")
But, as I said, everyone has different drive mappings. Therefore, I need to use the Network address instead of a drive letter. I tried this:
Set dbs = DBEngine.OpenDatabase("ServerMyBackEndDataTas k_be.accdb").
View 3 Replies
View Related
Nov 24, 2006
Hi,
I'm new in Access and facing a problem. Hope I’ll make myself clear.
I've a table "members" and I've created a form "member_form" based on this table (members).
1. I want to binde my form "member_form" with my table "member" so that the new data I store in form, automatically sotres in table. How can I do that:confused:
2. In form, I've fields "Member_ID" and "Member_Name". I want that when I type member_id in "Member_ID" field, the member name in "Member_Name" field come automatically. How can I do that???:rolleyes:
Thanks in advance
Hami Bjorn
View 1 Replies
View Related
Jun 16, 2005
Hey guys... I created a reaaaally simple wages calculation program some time ago in VB6. When you click the save button, the records are saved to a .txt file stored in the same directoy as the program itself.
However, I'd like to take this a step further and connect this program with some tables setup in an Access database. I know this has something to do with ADO, right? But not quite sure how to set this up..
Would it be possible to get some guidance on this? If you'd like to see the program, let me know.
Thanks for any input!
View 11 Replies
View Related
Sep 25, 2006
I have a table of customers and have table of stuff they can buy. Those two tables I use in the next table where their ordes get registred. I have and order form where I have a listbox. When I create a new order I want to have the customersname in a listbox but I want the customersID(primary key) returned in order table. How do I do that? :confused:
View 1 Replies
View Related
