Dynamic Link Tables Creation (1 Viewer)

[neoartz237]

The system Im currently working on demands importing a lot of datas from an Oracle Server, and by 'a lot', I mean 50K to 100K volumes of data.

Easily enough, I linked the necessary tables onto my mdb file and everything flew perfectly.

Unfortunately my boss demanded that I use an UDL file so that they can change connections on the fly and import said datas from a different Oracle Server. I know UDL files are one of the most unsecured connections to use, but hey they wanted it that way so I want/have to comply.

Is there a way for me to relink, or create linked tables from the connection I get from the UDL file?

Thanks in advance

 

[namliam]

You can surely relink excisting tables or Create new linked tables once you have the proper connect string.

The connection is a property of the table definition.

Lookup "tabledefs" in the access help and the "relink" or "refresh" (I forget) option of that table def. I think you can figure it out from there.

 

[neoartz237]

Yep　I tried that
I wrote:

Dim conn As ADODB.Connection
Dim tdf As TableDef
Set conn = New ADODB.Connection
conn.Open restorConStr

For Each tdf In CurrentDb.TableDefs
If tdf.Name = "HKR_02_TKYOKUMD01" Then
tdf.Connect = conn
tdf.RefreshLink
End If
Next




Whereas 'restorConStr' gets the connection string from the UDL file.

But all it does is prompting a Select Data Source window

 

[namliam]

Access is DAO not ADO, I dont think you can manipulate access tables using ADO....

I am not sure how the UDF connection translates into the Connection string used in access.... but something like this should work:

Dim tdf As DAO.TableDef
Set tdf = CurrentDb.TableDefs("YourTable")
tdf.Connect = "Some connectString"
tdf.RefreshLink

Then loop this for every linked table you have.

 

[neoartz237]

The ADO connection String gives me
Provider=MSDAORA.1;Password=aaaaa;User ID=aaaaa;Data Source=DBG2

Can I use that?

 

[namliam]

Try using the DAO code I provided using "Debug.print" to find out the connection string of the table.
Or just look at the properties of the table in de design view. The connection details are there as well.

 

[neoartz237]

Well apparently, they want me to stick to ADO connections and forget about the DAO. Asshats, I know.

I coded something where I make a connection, open an ADO.Recordset, put everything in it and append each data to my Access TempTable, but since Im dealing with huge volumes of data, it's pretty gawd dang slow.

Is there a way for me to like, put the data on my ADO recordset into a query or something so I can do Insert SELECT again?...

Thanks again

 

[namliam]

Nope ADO is memory stuff only.... no direct database interaction.

If you want to update table connections in the database so you can use linked tables and insert into... You have to use (as far as I know) DAO. Access native = DAO not ADO.

However you may be better of (depending on your situation) using Pass Through (PT)queries, you say you have large amounts of data... Then leave the data on the big database and run Queries on it instead of localy in Access using PT queries

 

[neoartz237]

Holy wow I've never heard of Pass-through queries, a research is absolute, thanks

 

[neoartz237]

Looks like I already am using it currently, just didnt know it was called Pass-through.
Here's how mine goes:

Public Sub dYomiKomi() '外データベースのデータを読み込んで、データベースに書き込む
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
'Dim tblNames(1 To 11) As String
Dim tblNames(1 To 12) As String '#02
Dim newName, InsStr, strModifier, connStr As String
Dim i, c, d As Integer
'////Fill up tables array////
tblNames(1) = "TKYOKUMD01"
tblNames(2) = "TTOKUKMD01"
tblNames(3) = "TTOKUIMD01"
tblNames(4) = "TSIHARMD01"
tblNames(5) = "TTORIKMD01"
tblNames(6) = "TSERMEMD01"
tblNames(7) = "TKANKAMD01"
tblNames(8) = "TSWKMSTD01"
tblNames(9) = "TUKKMKMD01"
tblNames(10) = "TCALENMD01"
tblNames(11) = "TSEKYUMD01"
'tblNames(12) = "TKANZAND01" '#02
On Error GoTo dame:
' For i = 1 To 11 #02
For i = 1 To 11

If doChecker(tblNames(i)) Then

Set cmd = Nothing
Set rst = Nothing
Set conn = New ADODB.Connection
Set cmd = New ADODB.Command
whoseError = ""
whoseError = "ACC"
conn.Open restorConStr
whoseError = "FOR"
DoCmd.RunSQL ("DELETE * FROM " & tblNames(i))
cmd.ActiveConnection = conn
cmd.CommandType = adCmdTable
cmd.CommandText = tblNames(i)
Set rst = cmd.Execute

If rst.RecordCount <> 0 Then

Do While Not rst.EOF
InsStr = ""
c = rst.Fields.Count
For d = 0 To c - 1

'////Check if Numeric or not////
If rst.Fields(d).Type = adNumeric Then
If Len(rst.Fields(d).Value) > 255 Then
strModifier = Mid(rst.Fields(d).Value, 1, 255)
Else
strModifier = rst.Fields(d).Value
End If
Else
If Len(rst.Fields(d).Value) > 255 Then
buff = Mid(rst.Fields(d).Value, 1, 255)
Else
buff = rst.Fields(d).Value
End If
If Left(buff, 1) = "'" Then
strModifier = "''" & buff & "'"
ElseIf Right(buff, 1) = "'" Then
strModifier = "'" & buff & "''"
Else
strModifier = "'" & buff & "'"
End If
End If

If d <> c - 1 Then
InsStr = InsStr & strModifier & ","
Else
InsStr = InsStr & strModifier
End If

Next d

' Debug.Print "INSERT INTO " & tblNames(i) & " VALUES (" & InsStr & ")" '### Debug ###
DoCmd.RunSQL ("INSERT INTO " & tblNames(i) & " VALUES (" & InsStr & ")")
rst.MoveNext

Loop

End If

End If

Next i

Set rst = Nothing
GoTo ok:
dame:
ErrHandler Err
Table_UnlockAll
Exit Sub
ok:
If msgYomiKomi = "All" Then
MsgBox "マスタ更新機能 読込処理" & vbCrLf & vbCrLf & " テーブル読み込み完了。", vbInformation, "読込処理"
Else
MsgBox "マスタ更新機能 読込処理" & vbCrLf & vbCrLf & " " & msgYomiKomi & "テーブル読み込み完了。", vbInformation, "読込処理"
End If
End Sub



What I do is I loop to each tables specified on an array, create an insert statement for each line of data, and append it to their respective access table clones.

Yes, I know its very very VERY slow. Extremely slow. Excruciatingly slow. It pains me seeing how slow it is honestly. Really.

Can I save the datas from my ADO recordset into a query, so that I can Still use the INSERT SELECT Statement?

Thanks

P.S. Sorry for the jumbled characters, those are my code comments.... Im Japanese... go figure

 

[namliam]

Please tell me your code doesnt really look like that!
But rather more like:

 if ... then
    ' do something
else
    ' do something else
end if

As far as I know any INSERT INTO command needs to have a (linked) table in the local DB. You could use some code to create a linked table, then use ADO to execute the insert query... But without the linked table... sorry... I dont know.

 

[neoartz237]

You're in luck, it doesn't. I just copy and pasted my code (w/ indentions) on the quick reply box and the indentions magically/rudely disappeared, this forums hates me so me thinks.

So basically I can't use INSERT SELECT without linking my tables inwhich I can't link my tables because they demanded me to use an ADO connection. And since only DAO can be used on linking tables, INSERT SELECTing is virtually impossible?

Wow... then it looks like Im stuck with this turtle slow method. I might actually be insulting some turtles by comparing their speed to this.

 

[namliam]

You're in luck, it doesn't. I just copy and pasted my code (w/ indentions) on the quick reply box and the indentions magically/rudely disappeared, this forums hates me so me thinks.

When posting code use "[ code ]" and "[ /code ]" around your code to keep the indentaions.

ADO vs DAO I said "as far as I know" so somebody may come along and prove me wrong.

I will act like a broken record:
WHY fetch all this data into your database? Asspecialy when all this data is so much... What do you need to do that cannot be done over ODBC connections or whatever?

 

[neoartz237]

Well they wanted to download all the datas to the access tables so that others who dont have an access to the oracle database can edit datas. Do some changes on the Access tables, get it approved then upload it back to the server.

Also they want the said datas in the mdb file so that they can email it to the other branches who doesnt have a server access to do some changes themselves and mail it back to be uploaded back in the server.

I tried suggesting on importing said datas using a link table, export in it excel, send the file, receive it back, import datas from excel, then upload it to the linked table, but they were very persistent about their method. What can a corporate peon do eh?

 

[namliam]

You can use linked tables to Oracle tables too... Bosses dont know anything! It is you who needs to make and maintain this stupid stuff.

 

[neoartz237]

Oh um actually Im not, I work at Fujitsu but they sent me to this god forsaken office for 2 weeks to help one of their clients on this particular problems. So maintaining this wont be my work next next week.

It's been ages since I used Access (last year) so I feel that I'm a little rusty.

Anyway, they wanted to connect to the server using an UDL file. UDL files meaning I have to use ADO Connection. Meaning no DAO Meaning no Link tables.

I thought I can link the tables before hand, then in run-time change the said linked tables' connection string from that of the UDL file's. But since UDL is ADO, and Link Table uses DAO Connection String, I scratched it.

I've showed them the bullet-time slow version and they were very unhappy by it's "blasting speed". :-/

Then I showed them way back the quick linked tables version and they just wont get off their high horses to listen to my explanations on why this version is exponentially slower than that one.

Can I bulk insert datas that I have in an ADO.Recordset to the Access tables?

 

[namliam]

Well an alternative would be to manually open the target table in ADO as well and add the records directly via code.

This will probably be somewhat faster... Seeing as the INSERT INTO is ussualy very slow.

 

[neoartz237]

Like appending each data one record at a time?

 

[namliam]

Yes, but that is not different from what you are doing now right??? Except you are using SQL instead of a recordset.

I have done/seen tests in the past where one way or the other made consideralble differences.

Also if you are running this on a network drive, try executing it on a local drive... That will make for a considerable bump in performance as well.

 

[neoartz237]

That's what Im actually running right now... it runs poorly Damn japs....