SQL DB to Access DB 1

[IknowMe]

I am trying to copy a Table from a SQL db to a Access db. I already have the connections made and am currently querying the SQLdb to populate the Mdb as follows. This takes a long time for some 1,600 records. Is there a way to somehow RecordSet = RecordSet without the loop?

Do While NOT SqlRS.EOF
MdbRS.AddNew
MdbRS("ID") = SqlRS("ID")
MdbRS("unit_number") = SqlRS("unit_number")
MdbRS("unit_name") = SqlRS("unit_name")
MdbRS("scheme") = SqlRS("scheme")
SqlRS.Movenext
Loop 'Do While NOT Rs.EOF

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[PHV]

Have you tried an Append query based on a linked table ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IknowMe]

PHV,

Thanks for the quick response.

Long story short

Our company mandated all db's be converted to SQL (We've done) but thus far haven't given any developer tools to work with. I inherited a script written in Extra Basic (I can convert vba code to eb so vba examples work fine) which was querying the SQL db and populating an identical (tables ect) Access db so we could 'see it' Problem was it was populating a string array with the sql query then populating the Access db with the array. Array got to large script quit working and was given to me to troubleshoot. MySqlKnowledge = Nil, but I'm learning.

I have a working fix (admitadly not close to the right way of doing things) but would like to make it quicker.

However, append queries are not always the most efficient way of adding records to another database. If you need to add all records and fields from one table to another table, the append query is not the best way to do it. Using Copy and Paste options in this case would be the best solution.

according to this the Copy/Paste is the route I want to pursue but I really doubt it.

Still Looking



Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[IknowMe]

Ultimately, I hope to get the proper tools to work with and redo the whole thing correctly. But for the time being I'm stuck with what I got

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[JoeAtWork]

Our company mandated all db's be converted to SQL (We've done) but thus far haven't given any developer tools to work with.

Literally I did *LOL* when I read that.

Thankyou for reminding me of the benefits to being self-employed.

 

[cmmrfrds]

The append query is the standard and most efficient way to add records from one table to another in most databases. Did you try what phv suggested? It will certainly be much faster than the example you show.

 

[IknowMe]

Here's what I have so far, again I'm trying make an identical copy of a SQL db (web based) to an Access dq (on my pc) with identical tables ect. I've made the Access db to match the SQL db and am trying to populate the Access Tables with the records from the SQL Table of the same name.

Sub CopyIt(ThisTable As String)

SqlRS.Open "SELECT * FROM " & ThisTable, SqlCon, 3,1
iScriptCnt = SqlRS.RecordCount

MdbRS.Open "SELECT * FROM " & ThisTable, MdbCon, 0,3

Do While NOT SqlRS.EOF
iCnt = iCnt+1
IE.document.getelementbyid("mainbanner").Innertext = "Copying SQL Data from " & ThisTable & " Table. Item " & str(iCnt) &" of " & iScriptCnt & "."
MdbRS.AddNew
For RecordNamesCnt = 1 to Ubound(RecordNames())
SQLString = RecordNames(RecordNamesCnt)
MdbRS(SQLString) = SqlRS(SQLString)
Next
SqlRS.Movenext
Loop 'Do While NOT Rs.EOF
SqlRS.Close
MdbRS.Close
End Sub

I've poked at MdbRS(SQLString) = SqlRS(SQLString).Clone and had success making a recordset clone in memeory but can't seem to get it to stick in the Access db so no success there.

I'm trying to grasp PHV's suggestion however I don't understand how to create the Link relationship between Tables outside of Access through the stand alone script. But I'm still working on it.

So assuming SQLdb contains one table named "FirstTable" with two fields "Field_One" and "Field_2" full of records and Accessdb contains the same Table and Fields (by the same name) how do you use Append Query to copy the records from Sqldb to Accessdb?

@Joe, glad you see some humor in the situation.


Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[PHV]

You seem to know what a connection is.
You have now to know what a linked table is.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[IknowMe]

That's a nice 'Chinese Proverb' there PHV

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.

 

[AlexCuse]

MrMilson - When you go to file --> get external data --> link tables

and then change 'Files of Type' drop down to 'ODBC Databases', you will get a pretty easy to follow wizard that'll help set up your linked table.

You then will choose your server, default database (for the connection), and finally tables you want to bring in.

This will let you directly query your SQL tables in access, and I think 'save you great pain in head'

(sorry attempt at another chinese proverb)

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[IknowMe]

Thanks to much proding to do this the correct way I finally got it worked out.

Still much learning curve to go young grasshopper, so till next time, Thanks

Wow, I'm having amnesia and deja vu at the same time.
I think I've forgotten this before.