• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Link tables in access >> MYSQL

Hi!

Have a table in access that have this Fields:

Name ---> Text
Numm ---> Autonumber
Fiks ---> Long integer

But this is not the same in MYSQL table, and give me an error 3219.

Does anyone know how i make this tables (access and MYSQL) the same ??

Please help...

Thanks

Tor
0
team2005
Asked:
team2005
  • 8
  • 6
  • 2
  • +1
1 Solution
 
jfkruegerCommented:
Access & MySQL do not have the same data types, thus when you link MySQL tables in Access, the fields will show a data type that Access would convert it to.  What is error 3219 and what is it that causes the error?
0
 
team2005Author Commented:
Hi!

I think the error message 3219 is caused by this Data type in the table in access.

Data Type --> Autonumber

What do i set this field to in MYSQL ??

Thanks for helping me out.

Tor
0
 
DreamMasterCommented:
identity is the type of field you need in MySQL, combined with identity_seed

Regards,
Max.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
DreamMasterCommented:
Actually, that's just an int value, that you can auto_increment under the "Extra" part of MySQL.

Regards,
Max.
0
 
team2005Author Commented:
Hi DreamMaster!

When i tryed to set the Field in MYSQL to Auto Increment
and make the Link in access, i still get problems in access.

Is there a way to change Data-fields the Linkd table in access from DAO-code in vb6.0 ?

Have had this problem a long time now, and must be finish with this ASAP.

If you can give me a sourcecode in vb6.0 that i can change this fields in the linked table in access.
I realy be happy.

Thanks

Tor
0
 
DreamMasterCommented:
What's the error you get?

Might be able to help you if I knew that...

Regards,
Max.
0
 
team2005Author Commented:
Hi!

The error is this:

When i use table in access (NO LINK) i start a report in access.
And everything working fine.

But when i try to link the table to MYSQL (with the same name as the table), and try to start the same report
It's stopp in a macro, that calls a function that is in a library (MDE-file)

Or if i quit access, and try to start access. I get this error message:

Run-time error '3219'
Invalid operation

What is going on.... Driving me crazy....

Thanks for helping me out.

Tor


0
 
DreamMasterCommented:
Can you get the data from both mySQL and Access using your program? If so, you can synchronize them using a bit of conversion.

Personally, I have the strange feeling, the error is not in your datatypes, but in the way you try to compare them.

Regards,
Max.
0
 
team2005Author Commented:
Hi!

Yes i can get data from both mysql and access.

How do i synchronize them ?

But why dosent it work when i link the table, and working just fine when i dont use
link ? Thats why i think the problem is datatypes...

Regards,
Tor
0
 
DreamMasterCommented:
The problem is more likely that you want to directly link them, while you could convert them to the datatype of the receiving database first...
0
 
team2005Author Commented:
Hi!

Please explane what you saying, don't anderstand... Sorry

What must i do then ?

Tor
0
 
DreamMasterCommented:
Well, I think you can't just link the MySQL to the Access database, so you will need to retrieve the data, convert them to text, integer and long int, or their respective counterparts and then save them into your database...
0
 
team2005Author Commented:
Hi!

But how is the code for doing this, please give me a code
that i can use. Thanks you

Hope you can give me the source in vb6.0 for this.

Thanks

Tor
0
 
jfkruegerCommented:
Also, go to your MySQL DSN and click on the Options button, there is a check box having something to do with "change bigint to int" or something like that, make sure that is checked and then try it.
0
 
team2005Author Commented:
Hi!

Tryed that, that dosent help...

Somthing else.....

Thanks

Tor
0
 
jimbobmcgeeCommented:
If you are trying to insert rows from your MySQL DB into Access, you cannot insert the AutoNumber field.  If the Access DB must have the same NUMM value as the MySQL one, you will have to change the AutoNumber to its Number equivalent.

J.
0
 
team2005Author Commented:
Hi!

Yes, but how is the sourcecode lock like for this ?

Thanks

Tor
0
 
jimbobmcgeeCommented:
OK, to go from MySQL to Access (assuming identically named fields in both), change your Access DB so that the field is a Long Integer not an Autonumber, then try:

      Dim oConnMySQL, oConnAccess, oRS, szFieldList As String, szValueList As String, n As Integer
      Set oConnMySQL = CreateObject("ADODB.Connection")
      Set oConnAccess = CreateObject("ADODB.Connection")
      Set oRS = CreateObject("ADODB.RecordSet")

      oConnMySQL.Open "connection string to MySQL database " & _
                                   "-- see http//www.connectionstrings.com for more details"
      oConnAccess.Open "connection string to Access database " & _
                                   "-- see http//www.connectionstrings.com for more details"

      oRS.Open "SELECT * FROM [MySQLTable]", oConnMySQL, adOpenStatic

          If oRS.RecordCount <> 0 Then

             oRS.MoveFirst

             szFieldList = "("
             For n = 0 To (oRS.Fields.Count - 1)
                  szFieldList = szFieldList & oRS.Fields(n).Name & ", "
             Next n

             szFieldList = Left(szFieldList, Len(szFieldList) - 2) & ")"

             Do Until oRS.EOF
                 
                 szValueList = "("
                 For n = 0 To (oRS.Fields.Count - 1)
                      szValueList = szValueList & oRS.Fields(n).Value & ", "
                 Next n

                 szValueList = Left(szValueList, Len(szValueList) - 2) & ")"

                 oConnAccess.Execute "INSERT INTO [AccessTable] " & szFieldList & _
                                                 " VALUES " & szValueList & ";"

                 oRS.MoveNext

             Loop

      oRS.Close

      Set oRS = Nothing
      Set oConnMySQL = Nothing
      Set oConnAccess = Nothing

HTH

J.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 8
  • 6
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now