I'm new to access and I'm trying to learn the functionality by creating a relational DB for US zip codes from an excel document. I've imported everything and I have the tables setup. Is there a way to run a MYSQL query like this:
UPDATE tbllocation, tblzipcode SET tbllocation.ZipID = tblzipcode.ZipID WHERE tbllocation.ZipID = tblzipcode.ZipCode;
in access 2007?
Thanks
I am building my frist database with MySQL as a backend to Access. I am using the ODBC driver and it looks very interesting. I have a question already though. I know that I can use the InnoDB engine and create relationships within MYSQL to enfro referential integirty, but should I? Or should I create the relationships in Access and let Access do the enforcing? Will my Access application report MySQL errors?
Thanks for the time.
I have a mysql database on my website (remote db). And i want to link to this database in acess(local bd) so that i can manipulate data.
How is it possible
Hey, Just wanted to throw the question out there, I already asked another member on their opinion on this but havent heard anything back yet, so im going to also throw the quesation out here as well and see what i get. Im new to databases and started out trying to learn Access with the intention that what i learned in Access i could use to learn MySQL, I figured access was a easier starting point to get my feet wet. I eventaully want to apply my databases to the web and im not sure but just wanted to find out. Does access have capabilities to manage a site where customers create an account on the site and log in and everything?? I was thinking access cant really do it and if it could it might be hard, so thats why i was looking into MySQL. I guess what im really asking is whats the difference between Access and MySQL, and what advantages and disadvantages do they have?? Everybodys opinion is welcome, it all helps me out. Thanks! ~[MikE]~
View 5 Replies View RelatedHello everybody,
Who can help me.
I want to access a MySQL database with ms access.
I cant seem to get it.
Please help me.
Thanks in advance,
John
Hello to all
this is my first post
i am havinga problem with my access database
so i started to converted it to MYSQL
but after converting it and running asp script it gives the following error
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E09)
[TCX][MyODBC]You have an error in your SQL syntax near 'SELECT tblTopic.Topic_ID FROM tblTopic WHERE tblTopic.Forum_ID = 4 ' at line 1
/mycplus/forum/Default.asp, line 335
and the orignal sql statement in ASP Forums is
strSQL = "SELECT " & strDbTable & "Author.Username, " & strDbTable & "Author.Author_ID, " & strDbTable & "Thread.Topic_ID, " & strDbTable & "Thread.Thread_ID, " & strDbTable & "Thread.Message_date "
strSQL = strSQL & "FROM " & strDbTable & "Author, " & strDbTable & "Thread "
strSQL = strSQL & "WHERE " & strDbTable & "Author.Author_ID = " & strDbTable & "Thread.Author_ID AND " & strDbTable & "Thread.Topic_ID IN "
strSQL = strSQL & " (SELECT " & strDbTable & "Topic.Topic_ID "
strSQL = strSQL & " FROM " & strDbTable & "Topic "
strSQL = strSQL & " WHERE " & strDbTable & "Topic.Forum_ID = " & intForumID & " "
'strSQL = strSQL & " ORDER BY " & strDbTable & "Topic.Last_entry_date DESC) "
'strSQL = strSQL & "ORDER BY " & strDbTable & "Thread.Message_date DESC;"
it was fully working in ASP + Access forums
Thanks
Plz help me
I have Microsoft Access on my desktop connecting to a MySQL database at my host, using an ODBC driver. I can read the data just fine. But when I try to add another record, it won't seem to do it.
Is it possible to update a MySQL database from MS Access on my desktop?
I have just one table. Very simple. Is it to do with primary key number generation or something?
Thanks,
Jon
Hi all,
hope someone can help me with this.
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.
All suggestions welcome.
ahrint
I have an existing MS Access/MySQL app that I need an experience developer to develop further. The existing application is fairly complex, so experienced developers only.
View 2 Replies View RelatedOn my development machine everything works great, but I know that I'm going to run into problems with distribution.
I don't think there is a way, but I'll ask. Is there any way to get around not having to install the ODBC MySQL drivers on each computer? One of the reasons I'm migrating to MySQL for a backend is the fact that I'll be going from 10 users to 150 users, each of which has a "locked down" PC, which would mean logging into each with Admin rights to install the driver. Not an impossible task, but a pain.
Is is possible to have a "shared" source for the driver? I have only a rudimentary understanding of that process.
Thanks in advance for any help that someone can provide, or some answers pointing me in the right direction.
I have converted my MS Access database tables to MySQL Server tables. I am trying to use the MS Access front end to cooperate with the MySQL Backend database. I have created a system DSN, without problems.
My issue is when I Open MS Access --> Open File --> Files of Type: ODBC Databases, nothing happens. It does not give me the option to connect to the MySQL database I just created. I also tried to import data by linking tables (again, choosing the ODBC databases option), and it does the same thing. Does anyone know what I may be doing wrong?
Could someone please help me?
At work we have a website with a mysql database attached to it. It keeps data such as customer name address postcode contact id etc.
Currently our main database is made in access.
I need to transfer data from the website database (which is in mysql) to our access database. And would like this to happen regulary. We have been quoted £1800 for someone to do this!
As this is a new job i would like to be able to do this myself, is it hard to do?
Could someone provide me with some information of how to do this? or where to start.
Much appriciated.
Hi There,
Whereabouts would I change the date format from the access database from yyyymmdd ?
I am trying to convert this table from access into mysql, and when I ouput to a csv file the date is in access.
Many Thanks
Chris
Hello
I“m working in an application using Access as FE and MySql as BE.
When I add some registers to this linked table, OK.
Problem: When I try to add a lot of registers (700.000!) it does“nt work and the msg is that the computer does“nt have memory resources to do the job.
If I try the same with Access tables it works fine.
MySql configuration, perhaps?
Thanks a lot
Renato
I have seen it done in the past but at the time my limited knowledge did not retain how to do it, it is connected through the odbc, im am running server 2000
what code do i use to connect and how do i set up the odbc
any help would be appreciated
Jabez
I don't know if this is thebest place for this, but here goes....
I have an Access FE using MYSQL 4.1 BE. I have several forms and PassThrough queries (using MYSQL syntax not supported or available in Access).
How can I use form field values to use as criteria for these queries?
FOr example, in a pure Access database. I may have a form 'Employees' with a text box 'EmployeeName'. THen have a query....
SELECT * FROM tblEMployees WHERE EmployeeName = [Forms]![Employees]![EmployeeName]
If I were to add this variable to my pass-through queries, it errors. How can I do the same thing, passing my form fileds to an Access Query? I am trying to do this right in the SQL view of the queries, not in VBA or Macros or anything.
Thanks,
Scott
If I want to distribute a front end to connect with a MySQL back end ... do I need to set up the system DSN on each workstation that will be using the front end?
View 4 Replies View RelatedI am trying to remove random characters from a field. The field [assycode] contains a string similar to say, FGEJBF1 or ABFGYRUKC I want to remove any occurrence of "F1" normally at the end of the string but not always at the end. I used: Like "*f1*" to find the correct records, that worked fine, I then used [Assycode]-" f1" in the update to box, It wants to update 146 records I click ok then it says It couldn't due to a type conversion error. Just messing around I tried adding "F1" to these records using [Assycode]+" f1" and it worked fine. Can anyone point me in the right direction?
Thanks in advance
Wayne
I've found myself having to start using Access due to record limits. My reports come to me in Excel and go out in Excel, but by sheer quantity I need to store records in Access.
My problem is in an Update Query:
I import records into a 'DoNotDelete' table and then run an update query to the 'CallsEntered' table. After that I run a Delete query to clear the 'DoNotDelete' table for next time.
The problem is that 'CallsEntered' table is updating the 'DoNotDelete' table.
I know this is simple, but I've done what the "manuals" say to (as far as I can tell). Your time is appreciated.
UPDATE DoNotDelete INNER JOIN CallsEntered ON DoNotDelete.[Work Order Nbr] = CallsEntered.[Work Order Nbr] SET CallsEntered.[Wo Status] = CallsEntered![Wo Status], DoNotDelete.[Date CheckIn] = CallsEntered![Date CheckIn], DoNotDelete.[Assigned Installer] = CallsEntered![Assigned Installer];
I have summarized a table(a) of 589 records into a table of 119 records(b).
I am trying to calculate a factor in (a) as a percentage of the total in (b) for each record in (a).
(a) can have multiple records for each corresponding, summarized record in (b).
I do this by dividing each of the many records in (a) by a summarized record in (b) with a common field in both.
Basically, I am dividing
Round(([(a)]![netchrg] / [(b)]![netchrg]),8)
All I get is 0.00000000
I have checked my join relationships.
I started by checking the 2nd bubble. It did not work. Eventually, I have tried selecting all three bubbles on the joins and that does not get me any different values.
I would have thought that the 2nd bubble would have given me all of (a) and match it to the the records is (b) for the calculation.
I have even tried eliminating the joins completely but I still get a 0 value.
I have verified that both tables have values in the netchrg field.
Suggestions?
Thanks.
Hi all,
I am generating a multi source concatenated code with an update query in Microsoft Access. I am wondering if there is a way to cut the update to only new entries rather than updating all records each time the query is run.
Any helpers for this problem?
Hi ALL,
I have 2 tables, trying to update one table filed by using max(onefiled) from other table.
my qyery is as follows:
my tables are tblSubFlowForecast and tblPickCalendar
I have to update one field in tblSubFlowForecast by taking max(onefiled) from tblPickCalendar.
UPDATE tblSubFlowForecast INNER JOIN tblPickCalendar ON tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry SET tblSubFlowForecast.[Latest Replen Date] = ( SELECT max(Pick) FROM tblPickCalendar WHERE Type="Replen");
I am getting error saying that "Operation must be an updateable query" :(
I have also tried in other way:
UPDATE tblSubFlowForecast SET [Latest Replen Date] = (SELECT max(Pick) FROM tblPickCalendar WHERE tblSubFlowForecast.Delivery=tblPickCalendar.Delive ry AND tblPickCalendar.Type="Replen" ) FROM
tblSubFlowForecast,tblPickCalendar
This is giving me syntax error (missing operator) :(
Can any body tell me why it is giving error?
Thanks in advance
Hello,
I need help in updating a field in a query result.
I have 3 tables connected in a query
tblEmployee -> tblODF
TblStatus ->tblODF
tblQueue ->tblQueue
In the query, i have these results
UserName from tblEmployee
Status from entries in tblStatus
Queue from entries in Queue
Here's my problem, let's say i have 3 records, 2 of them have a username John:
1. John
2. Other
3. John
I want to update record 3 from John to other, what happens is that record 1 also gets changed. so now i have the following:
1. other
2. other
3. other
How do i prevent access from updating all records?
thank you.
I want update a table (in Access 2010) based on the results from a pass-through query that pulls data from an old Fox Pro database.
With the following query I push data into my access table (everything works fine):
Code:
INSERT INTO Bolle ( esercizio, numero_documento, ...)
SELECT Bolle_Arca.[esercizio], Bolle_Arca.[numero_documento], Bolle_Arca.[testata_codice], ...
FROM Bolle_Arca
WHERE
Bolle_Arca.[testata_codice] NOT IN (SELECT Bolle.testata_codice FROM Bolle)
When I try to update the same table using the same pass-through query:
Code:
UPDATE Bolle INNER JOIN Bolle_Arca ON Bolle.riga_codice = Bolle_Arca.riga_codice
SET Bolle.esercizio = Bolle_Arca.esercizio
, Bolle.numero_documento = Bolle_Arca.numero_documento, ...
WHERE Bolle.spedito = False
and Bolle.data_invio_mail_spedito is null
and (Bolle.tracking is null or Bolle.tracking = '')
and Bolle.testata_codice = Bolle_Arca.testata_codice;
I get this error: "Operation must use an updatable query"
I have a database that logs complaints. I've added a field to calculate the age of a complaint based on the date received and the date resloved. To update this field I have an update query that runs after someone closes an update form. My problem is that I recieve two dialog boxes one that confirms that I'm will be updating the table and another that tells me how many rows were updated. I would not like those boxes to appear so the update would look transparent.
View 1 Replies View RelatedI am just a guy in a cube that likes Access and have been able to make it do some great things but am by no means a programmer so...
It seems like the next step is to go with a SQL be and Access fe (network not internet applications) not that what I have isn't doing the job, I just want to swim with the other fish and not be left behind.
So if I could just download a program called SQL that would be great but there are various kinds? SQL Server, MySQL and who knows what else.
I don't know what my company will migrate to in the future (we are currently using A97 and upgrading to 03 soon) and most people here don't even know what a database is. I thought I'd give MySql 5.0 a look, dowloaded it and installed it (don't even know if I answered all the install questions correctly) and started it up and I felt like I was looking at a WordPerfect 5.1 screen all over again (blank blue screen and that was it for those who do not remember). All I saw was a command prompt with "mysql>" that's it.
Now I don't mind doing my reading but I'd just like to know I am going in the right direction before I spend all this time only to find out I should have gone another way.
Is there an easy start method or do I just have to bump and feel my way through all this?
It seems once you leave the calm cool shallow waters of Access you are suddenly faced with strange deep sea creatures called "Programmers".