Login or Sign Up to become a member!
LessThanDot Site Logo

LessThanDot

A Technical Community for IT Professionals

Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

LTD Social Sitings

Lessthandot twitter Lessthandot Linkedin Lessthandot facebook Lessthandot rss

Note: Watch for social icons on posts by your favorite authors to follow their postings on these and other social sites.

Highly Rated Users

Forum
No Posts Rated

Top 50
Given
Received

Links

Wiki
Blog

Forum Statistics

Users
Members:
1590
Members Online:
4
Guests Online:
8

Total Post History
Posts:
80550
Topics:
18446

7-Day Post History
New Posts:
7
New Topics:
1
Active Topics:
7

Our newest member
langm8029

Other

FAQ
All times are UTC [ DST ]

Google Ads

FAQ: Access and MySQL

Forum rules
Please:
- include the version of Access you are using.
- include the exact details of any error.
- highlight or draw attention to the line of code where the error occurred.
- read the FAQs, where these and other points are mentioned:
Topic565: General FAQ for Access
Please wait...

FAQ: Access and MySQL

Postby Remou on Sun Dec 14, 2008 10:38 pm

Access Version: 2000

You can insert data from Access into MySQL using DSN.

You will need:

DSN=Suitable system DSN for MySQL

Then, depending on your set-up, you can incude:
Database=MySQLDatabaseName;
Trusted_Connection=Yes;

  1. Set db = CurrentDb
  2. strSQL = "Insert INTO [ODBC;DSN=DSNName;].NameOfMySQLTable " _
  3. & "Select AnyField As NameOfMySQLField FROM AnyAccessTable;"
  4.  
  5. db.Execute strSQL, dbFailOnError
  6. End Sub


I have not been playing around with this very long, but as far as I can tell, you must have matching field names, however, you can use aliases to make the names match.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948
LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948
LTD Gold - Rating: 948
 
Posts: 5302
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: FAQ: Access and MySQL

Postby Emtucifor on Sun Dec 14, 2008 11:38 pm

DNS <> DSN
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030
LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030
LTD Gold - Rating: 1030
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated

Re: FAQ: Access and MySQL

Postby Remou on Sun Dec 14, 2008 11:44 pm

:blush: I though dyslexia was one of the few problems I had avoided. Oh well, you are never to old to forget.
Stop quoting laws to us. We carry swords.
User avatar
Remou
LTD Admin
LTD Admin
LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948
LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948LTD Gold - Rating: 948
LTD Gold - Rating: 948
 
Posts: 5302
Joined: Sun Oct 14, 2007 11:26 am
Unrated

Re: FAQ: Access and MySQL

Postby Emtucifor on Mon Dec 15, 2008 2:48 am

I really thought you meant you could use DNS somehow. :D
God cries a little bit every time someone builds a database.
User avatar
Emtucifor
Guru
Guru
LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030
LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030LTD Gold - Rating: 1030
LTD Gold - Rating: 1030
 
Posts: 2835
Joined: Fri May 30, 2008 9:30 pm
Location: Bellingham, WA
Unrated