S
Stuart
I'd been looking for months for the simplest way to import a table
from a remote Access DB, using vba code, & working whether or not the
remote DB is password protected. I'd been using linked tables built
"on the fly," which worked generally, but created problems in some
situations. The below posting from Gary Walter was a huge help. I've
now created a sub:
Sub importTable(fromDB As String, fromTbl As String, toTbl As String,
_
passwd As String)
Dim strSQL As String
'imports a table from remote DB. fromDB is path/filename of source DB.
'fromTbl is name of table in source. toTbl is name to be given to
'table after it's imported to this DB. passwd is DB password of source
DB.
DoCmd.SetWarnings False
strSQL = "SELECT * INTO " & toTbl & " FROM " & fromTbl & " IN ''
[MS Access;"
If passwd <> "" Then
strSQL = strSQL & "PWD=" & passwd & ";"
End If
strSQL = strSQL & "DATABASE=" & fromDB & "];"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
which has worked great in all cases.
Stuart Resnick
(e-mail address removed)
http://home.comcast.net/~sresnick2/mypage.htm
**********Gary's original post**********
From: Gary Walter ([email protected])
Subject: Re: Invalid password when using IN clause
Typically one would just link to the table in the remote
database and save yourself time and grief.
If you do not wish to do it that way for some reason....
The target of an INSERT sql can be a table
* or a query.*
To create a query to the remote, password-protected
table....
- Start a new query (don't choose any tables).
- In SQL View, type in
SELECT * FROM Table1
- Right-mouse click in empty part of upper pane and choose
"Properties"
-in "SourceConnectStr" row type in
(following example will use 'homer' as your password
--change 'homer' to your password)
MS Access;PWD=homer;DATABASE=c:\mydb1.mdb
-save your query with name like "qryRemote"
- test query that returns all of Table1
("Source Database" row in Properties should be blank)
-use "qryRemote" in your INSERT query instead of "Table1"
INSERT INTO qryRemote SELECT Field1, Field2
FROM Table2 WHERE Field1=anynumberhere
All thanks go to "Doug" who demonstrated this remote
db w/password query technique in this newsgroup.
Good luck,
Gary Walter
from a remote Access DB, using vba code, & working whether or not the
remote DB is password protected. I'd been using linked tables built
"on the fly," which worked generally, but created problems in some
situations. The below posting from Gary Walter was a huge help. I've
now created a sub:
Sub importTable(fromDB As String, fromTbl As String, toTbl As String,
_
passwd As String)
Dim strSQL As String
'imports a table from remote DB. fromDB is path/filename of source DB.
'fromTbl is name of table in source. toTbl is name to be given to
'table after it's imported to this DB. passwd is DB password of source
DB.
DoCmd.SetWarnings False
strSQL = "SELECT * INTO " & toTbl & " FROM " & fromTbl & " IN ''
[MS Access;"
If passwd <> "" Then
strSQL = strSQL & "PWD=" & passwd & ";"
End If
strSQL = strSQL & "DATABASE=" & fromDB & "];"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True
End Sub
which has worked great in all cases.
Stuart Resnick
(e-mail address removed)
http://home.comcast.net/~sresnick2/mypage.htm
**********Gary's original post**********
From: Gary Walter ([email protected])
Subject: Re: Invalid password when using IN clause
Typically one would just link to the table in the remote
database and save yourself time and grief.
If you do not wish to do it that way for some reason....
The target of an INSERT sql can be a table
* or a query.*
To create a query to the remote, password-protected
table....
- Start a new query (don't choose any tables).
- In SQL View, type in
SELECT * FROM Table1
- Right-mouse click in empty part of upper pane and choose
"Properties"
-in "SourceConnectStr" row type in
(following example will use 'homer' as your password
--change 'homer' to your password)
MS Access;PWD=homer;DATABASE=c:\mydb1.mdb
-save your query with name like "qryRemote"
- test query that returns all of Table1
("Source Database" row in Properties should be blank)
-use "qryRemote" in your INSERT query instead of "Table1"
INSERT INTO qryRemote SELECT Field1, Field2
FROM Table2 WHERE Field1=anynumberhere
All thanks go to "Doug" who demonstrated this remote
db w/password query technique in this newsgroup.
Good luck,
Gary Walter