In the Access application that I have developed for a user, the application is supposed to establish the ODBC connection to an external database when the application is initiated. On my PC, the application works. On an older PC, the application works. However, on the User's PC and on the new one that we are building for her the linking to the external tables does not work. I have displays for the tables that she is to link to as the program is doing the linking. Sometimes, on the PC for her use, the first table will not link and the rest will. Sometimes, none of the tables will link. Sometimes, the first half will link and the second half won't. I am stymied. Below is the logic that I use to make the connections at the beginning of the process. Any suggestions?
Dim strTable As String
On Error GoTo Assign_Error
'MsgBox ("Click on OK and Please be Patient as the program links to PeopleSoft tables.")
' NOTE : Do not unlink the tables as you will have to manually relink the tables
End Function
Function link_table(strTable As String)
Dim dbs As Database
Dim tdfLinked As TableDef
Dim strDatasource As String
Dim strDatabase As String
Set dbs = CurrentDb
Set tdfLinked = dbs.CreateTableDef(strTable)
' tdfLinked.Attributes = dbAttachSavePWD
' Check to see if table already exists and if it does then do nothing
' For Each tdfLinked In dbs.TableDefs
' If tdfLinked. = strTable Then
tdfLinked.Connect = "ODBC;UID=sysadm;PWD=sysadm;DSN=" & strDatasource & ";"
tdfLinked.SourceTableName = strTable
tdfLinked.Attributes = dbAttachSavePWD
dbs.TableDefs.Append tdfLinked
DoCmd.Close acForm, "Message"
' Exit Function
' End If
' Next
' [Forms]![Message].SetFocus
' [Forms]![Message]![Message] = strTable & " Not Found for Linking"
' [Forms]![Message].Repaint
Link_Error:
' if the error is simply that the item to delete isn't actually
' there to delete, we want to skip it.
If Err = 3265 Then
Resume Next
End If
' otherwise we want to show what the error is, and then exit.
MsgBox Error$
Resume Link_Exit
End Function
Function unlink_table(strTable As String)
Dim dbs As Database
On Error GoTo Unlink_table_Error
Set dbs = CurrentDb
dbs.TableDefs.Delete strTable
Unlink_table_Exit:
Exit Function
Unlink_table_Error:
' if the error is simply that the item to delete isn't actually
' there to delete, we want to skip it.
If Err = 3265 Then
Resume Next
End If
' otherwise we want to show what the error is, and then exit.
MsgBox Error$
Resume Unlink_table_Exit
while updating the databse on the server using a ftp client users get the following error on asp page.
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 0xf7c Thread 0x194 DBC 0x899e70c Jet'. /myscript.inc, line 25
line 25 has no error except the following code: conn.open "driver={Microsoft Access Driver (*.mdb)};dbq=" & DBPath
Is there anyway to check if a connection can not be established or the database is being updated asp page sends friendly message to user that the database is being updated or temporary down or please try again in few seconds.
Any help and idea will be highly appreciated in advance
Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver]General error Unable to open registry key 'Temporary (volatile) Jet DSN for process 0x818 Thread 0xa74 DBC 0x5f5c1024 Jet'.
I get the above error when I run my login form can anyone help? it points to the code below
<% Response.Buffer = true %> <% Session("DatabasePath") = "MoneygallGAA.mdb" If Request.Form("btnLogin") = "Login" AND Request.Form("txtName") <> "" _ AND Request.Form("txtPassword") <> "" Then
'-- Declare your variables Dim DataConnection, cmdDC, RecordSet Dim RecordToEdit, Updated, strUserName, strPassword
'-- Create object and open database Set DataConnection = Server.CreateObject("ADODB.Connection") DataConnection.Open "DRIVER={Microsoft Access Driver (*.mdb)};" & _ "DBQ=" & Session("DatabasePath") & ";"
Set cmdDC = Server.CreateObject("ADODB.Command") cmdDC.ActiveConnection = DataConnection
'-- default SQL SQL = "SELECT * FROM Login"
If Request.Form("txtName") <> "" Then SQL = "SELECT Login.* FROM Login " & _ "WHERE Login.userID='" & strUserName& _ "' AND Login.password ='" & strPassword & "'" End If
cmdDC.CommandText = SQL Set RecordSet = Server.CreateObject("ADODB.Recordset")
If Not RecordSet.EOF Then Dim struserLevel struserLevel = RecordSet.Fields("userLevel") Session("userLevel") = struserLevel Else 'The user was not validated... 'Take them to a page which tells them they were not validated... Response.Redirect "default.htm" End If End If %>
I'm somewhat new to working with ASP and very new to databases, MS Access, and SQL.
What works is opening my .mdb file, which connects through an ODBC DSN to get dynamic data. Nothing fancy.
What I'm trying to do is create a web interface via ASP to access certain data and fields in the db.
Here are the super-noob questions...Do I need a version of MS Access and the .mdb file on my webserver (where the .asp file is)? I don't have the ODBC DSN set up on the webserver (which is linux). Does that need to be done before anything will work?
Let me know if code snips would be of any assistance. Thanks much, in advance!
I've just set up my Access on a new PC and now when I try to connect to my MySQL database through odbc, Access crashes without any error messages, just the standard microsoft error reporting message.
I have a simple database that has in the same record in a table 3 fields of last names that are populated by entering the data in a form. There could be the same last names in each of the 3 fields. (ie; John Doe, Mary Doe, Little Doe) My problem is that I want to make a report by clicking on a button searching by the last name (Doe) and consider each of the 3 last names in my search criteria. Does anyone know how this can be accomplished as not to miss any of the (Doe's in this case)? Please help if you can I know it sounds confusing.
I am finding it more and more difficult to answer questions lately, is it just me or has the quality of the questions gone down?
In some cases it appears that the "question" is more of a request to have something done for them than actually trying to solve a problem that they are having with a learning process.
I am having difficulty creating code that will compare a date in one table (or query) in the [Expire] field and subtracting two months from the date and automatically placing the newly calculated date into the table (or query) in the [Notify Expiration] second field. For example:
Please note that for the month of January and February, the subtraction is minus two for these two months and the subtraction is minus one for the year. All other subsequent months just subtracts two for the months leaving the year intact. I would appreciate any help that will progmatically accomplish these calculations.
I am having trouble with opening an existing form in ms access. Any idea what could be the problem? Even if I am not able to retieve the whole form is there a way I can retrieve all the event procedures associated with the form?
I have recently upgraded from Office XP to Office 2007. I had an Access database which worked fine in Access XP. In it I had a table linked to an Excel spreadsheet. When I entered data on an access form, it updated the Excel spreadsheet [source document] accordingly. This does not seem to work in Access 2007. The linked table option now does not permit data entry or amendement. The other "Get external data" options create a table in Access which also does not update my source Excel document. The idea is to enter data in Access using a form. This data is placed in an excel spreadsheet. Excel gets some lookup values, and then enters these in a Word mail merge. Without the functionality of a linked table and data entry, I cannot produce new reports.
I am having difficulty getting a query to work, there's probably something really simple I'm missing. . . I can get the Union function to work but I can't get it to create a table for me. The basic query looks like this:
INSERT INTO test SELECT * FROM [Design] WHERE (Design.CID Not Like "*-*") UNION SELECT * FROM [Release] WHERE (Release.CID Not Like "*-*")
It works fine and shows me the table I want without the first line, but I would like it to input into a new or existing table. Any help would be greatly appreciated!
Hi, I’m having a problem creating a form for easy data entry. Let me give you a little background. I have a database for a tutor program, students request a tutor for a class, and then I fill out a follow up report to find out if they are being tutored or changed their minds. Next I enter the contact information indicating the date and time of their tutoring session.
Students can be tutored in more than one class but they can only have one tutor for each class.
I have a form where I enter the tutor request info and sub forms for their classes, follow ups, and sessions. Ideally I would also like a form just to enter the session info. I want a combo box to select the tutor and then a sub form to select the tutee with a combo box which will auto fill the Subject, and Course fields, and field to enter the date and contact hrs.
I think the problem is with my relationships. I’m just kind of stuck. I think I need to create a form based on a query but I haven't done that before. Any help would be appreciated. Attached is a screen shot of my relationships and of my current form for your reference.
Is it possible to connect a Document to the Access Database. To have a button beside the field in the form allowing you to browze and connect the document. If not does anyone have a way around this. Any help would be well appreciated.
Hi We have a database implemented in MS-SQL server 2000 on a local machine. I want to use some of tables in my access (or excel) program. Can I link to the table?
I'm having a problem in Access where I'm trying to connect 2 tables together. On one table is all the information of the person, the other table is a list from 1-50. That list is a drawing of all the peoples ID number for a drawing. When I type in their ID next to what order they got picked in is there any way where All of their information comes with that ID number they have? I really need help on this.
Hi there, I'm currently writing an accounting system and i got stuck with this section of trying to produce the account name in a query.
i have four tables
tblPurchase PurchaseID -Number
tblAccounts AccountID - Number AccountName - Text, usually has names like asset, cost of sales, expense, telephone, electricity, etc...
tblItems ItemID, Number AssetNum (tblAccounts.AccountName foreign key1), Number ExpenseNum(tblAccounts.AccountName foreign key2), Number IncomeNum(tblAccounts.AccountName foreign key3), Number
tblPurchaseLines PurchaseNum (foreign key for tblPurchase.PurchaseID), Number ItemNum (foreign key for tblItems.ItemID), Number
My question is how can i generate the query with the following fields:
PurchaseID ItemID AccountName of AssetNum AccountName of ExpenseNum AccountName of IncomeNum
I am aware that the query would produce three PurchaseID's for every ItemID it would encounter for every PurchaseLine.
So, here's my problem: I'm doing a database on my DVDs, and I wanted to add as much info as I can about them. But I have some problems with "actors", as you know one actor can be in several movies and one movie has several actors.
I want to put the actors in their own table and movies in their own. I know how to link them together, but I don't know how to link them together so that actors could be in more than one movie and movie could have more than one actor.
Hope that even someone understands my question since my english ain't so good..
I work in an office where we do testing with clients . I want to create a database that can create a unique report for each client on the testing results.I envision a database with at least three tables. The first table would be client demographic information with a unique ID field (CID). The second table would be the Appointment information (date, referral source, etc.). The third table will have the test results (although I'm wondering if I should have a table for each test).
Sometimes, we see clients more than once, and so need the ability to have more than one appointment record for each client. For each appointment, we would record test results.I have created a one-to-many relationship between the Client Demographics and the Appointment tables. When I created the form for the Client Demographics, I inserted a subform for the Appointment. That works great. The CID automatically transfers to the Appointment record and instantly connects the two.How do I connect the Test Results table so that the CID automatically transfers to the Test Results record as well as the Appointment Date field from the Appointment Table.
Before we moved webhosting company we could have external access to our MySql database which was great as our MS Access system would periodically pull data from our MySql site (namely customer enquiries which were entered via a form on our website) and dump them in our MS Access live quotes table all automatically.
With our current host (which seems to be the norm from my web-searches) they only allow local access to the MySql system.
My question is, how can I oversome this hurdle without having to move hosting company. I really don't want to move again as it's a real pain.
I'm using flash mx and MDM zinc which i assume connects using the JET rather than ADO database engine.
For the life of me cannot get a simple update to work due to the dates involved, lost count how many hours have gone by trying!!!
Within access I can easily switch quotes and hash symbols and all SQL THREE SQL queries work without problems on my database i.e.
UPDATE UserOverallResults SET Results = 20 WHERE UserName ='Robbie' AND Quizdate= '19/2/2008 12:15'
UPDATE UserOverallResults SET Results = 20 WHERE UserName ='Robbie' AND Quizdate= "19/2/2008 12:15"
UPDATE UserOverallResults SET Results = 20 WHERE UserName ='Robbie' AND Quizdate= #19/2/2008 12:15#
Within the database the fields are defined as :- Results (Integer) Username (Text) and QuizDate (Date/Time) within MS ACCESS DB schema.
in Flash MX TextDate param is a STRING and All parmaters are populated appropriately (see this later in error message).
Example of existing date data stored in database in format "19/02/2008 16:48:11" as a DATE/TIME field.
My code within in flash is :-
mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate = '" +textDate + "' "); I've tried with and without Hashes still no joy ..
mdm.Database.MSAccess.runQuery("UPDATE UserOverallResults SET Results = " + UserScore + " WHERE UserName = '"+ UserName +"' AND QuizDate = Format( '"+ textDate +"', "#yyyy\-mm\-dd hh:nn:ss#")"); The error message I get from the code above is ..
SQL Query has failed for the following reason: Data type mismatch in criteria expression SQL statement: UPDATE UserResultsOverall SET Results = 30 WHERE UserName = 'd' AND QuizDate = Format('19/2/2008 19:4:35' ,"#yyyy-mm-dd hhnss#")
My text string removes the leading zeros not sure if that would cause an issue or not (its doesnt in MS ACCESS running query) ...
Anyone have any ideas??? Losing my mind here !!
PS I had problems using '&' instead of '+' to concatenate fields.
is there a possibility to connect two different databases??? I mean is, I have a table called PERSON. under PERSON, there are fields called PERSON NAME, BIRTHDAY and ADDRESS. another table is called SEMINARS, under it are DATES FROM, DATES TO and TITLE.
I want to combine one of the records in PERSON to the 5 records in the SEMINARS. is there a possibility to do that???
to include in the information, there are 10 records in the PERSON and there are 75 records in the SEMINARS. and I want to add more records in SEMINARS in any of the records in PERSON in the near future.
how do you connect fields from one table to another? what im trying to do is to connect two fields from product! product id and order details! product id. the one in the order details table should equal whatever i input in the product table.
A school table is there in old and new data base, if i give school key as 001 (which is the column of school table) i need to compare old database school table "001 key" and new database school table "001 key" and if it is not matched it should be displayed. Please give me detailed dicription with example.
I have a little problem: And hope somebody can give me an answer.
I have two tables, one form and on the form I have two textboxes. One of the textboxes should be connected to ONE table and field, and the other textbox to the OTHER table and field.
I tryed to conect through the expression builder but had not much luck! See code below.
This code comes up in the ControlSource property section and textbox also: =[tbl_CompanyContact]![CompanyPhoneNumber] =[tbl_PrivateContact]![PrivatePhoneNumber]
Where do I set in properties the connection to different tables i.e. tbl_CompanyContact / tbl_PrivateContact and where the connection to the field CompanyPhoneNumber / PrivatePhoneNumber.
