autoexec string connect to db

[Guest]

I would like to connect to Progress db on startup of my access application.
In a nutshell I query a subset of master data off a production server to run
my Access application against so I don't bog down my production environment.
I would like to create a connection string and run the query automatically at
startup hence the autoexec.

In the past I just run a make-table query in the autoexec, but that prompts
for the remote db password. I would like to have one of the following
solutions:

1. Be able to put the remote db connection in the autoexec macro and just
run the make-table query
2. Use a VBA function for the problem.

I have written the following CBA function, but it doesn't populate the
table. I'm very new to functions so I'm at a loss how to get it to work.

Public Function IndARSC()

Dim CurrConn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim CurrDb As Database
Dim strConn As String
Dim strSQL As String

Set CurrDb = CurrentDb
Set CurrConn = New ADODB.Connection
strConn = "DSN=myDSN;User ID=myid;Pwd=mypsswd;"

CurrConn.Open strConn

strSQL = "SELECT * FROM PUB.arsc"
CurrConn.Execute strSQL
rs.Open "SELECT * FROM PUB.arsc", CurrConn, , , adCmdText

Do While Not rs.EOF

DoCmd.RunSQL "SELECT [PUB.arsc].[cono], [PUB.arsc].[custno],
[PUB.arsc].[name] INTO IndexARSC FROM [PUB.arsc]"
(PUB_arsc.cono=1))"
rs.MoveNext
Loop

End Function

Any suggestions would be much appreciated. Thanks.

 

[LanceMcGonigal]

I suggest instead of running the sql statement to load the table that you
use vba. Loop through the main set, read columns over to the local set, and
close out.

I like using vba instead of the sql because I can see inside the black box
as to what the process is doing. Just my thoughts.

cheers

 

[Guest]

Lance,

Thanks for the suggestion. Can you float me a little air code to get me on
the right track? I'm fairly inexperienced coding & not sure exactly what
you're talking about.

I suggest instead of running the sql statement to load the table that you
use vba. Loop through the main set, read columns over to the local set, and
close out.

I like using vba instead of the sql because I can see inside the black box
as to what the process is doing. Just my thoughts.

cheers

I would like to connect to Progress db on startup of my access application.
In a nutshell I query a subset of master data off a production server to run
my Access application against so I don't bog down my production environment.
I would like to create a connection string and run the query automatically at
startup hence the autoexec.

In the past I just run a make-table query in the autoexec, but that prompts
for the remote db password. I would like to have one of the following
solutions:

1. Be able to put the remote db connection in the autoexec macro and just
run the make-table query
2. Use a VBA function for the problem.

I have written the following CBA function, but it doesn't populate the
table. I'm very new to functions so I'm at a loss how to get it to work.

Public Function IndARSC()

Dim CurrConn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim CurrDb As Database
Dim strConn As String
Dim strSQL As String

Set CurrDb = CurrentDb
Set CurrConn = New ADODB.Connection
strConn = "DSN=myDSN;User ID=myid;Pwd=mypsswd;"

CurrConn.Open strConn

strSQL = "SELECT * FROM PUB.arsc"
CurrConn.Execute strSQL
rs.Open "SELECT * FROM PUB.arsc", CurrConn, , , adCmdText

Do While Not rs.EOF

DoCmd.RunSQL "SELECT [PUB.arsc].[cono], [PUB.arsc].[custno],
[PUB.arsc].[name] INTO IndexARSC FROM [PUB.arsc]"
(PUB_arsc.cono=1))"
rs.MoveNext
Loop

End Function

Any suggestions would be much appreciated. Thanks.

 

[LanceMcGonigal]

Here ya go:

set inrecordset = currentdb.openrecordset("myinputset")
set outrecordset = currentdb.openrecordset("myoutputset",dbopendynaset)

function testcode()

do while not inrecordset.eof
outrecordset.addnew
outrecordset!field1 = inrecordset!field1
...
outrecordset.update
inrecordset.movenext
loop

exit function

end function

cheers

Lance,

Thanks for the suggestion. Can you float me a little air code to get me on
the right track? I'm fairly inexperienced coding & not sure exactly what
you're talking about.

I suggest instead of running the sql statement to load the table that you
use vba. Loop through the main set, read columns over to the local set, and
close out.

I like using vba instead of the sql because I can see inside the black box
as to what the process is doing. Just my thoughts.

cheers

I would like to connect to Progress db on startup of my access application.
In a nutshell I query a subset of master data off a production server

to
run

my Access application against so I don't bog down my production environment.
I would like to create a connection string and run the query

automatically
at

startup hence the autoexec.

In the past I just run a make-table query in the autoexec, but that prompts
for the remote db password. I would like to have one of the following
solutions:

1. Be able to put the remote db connection in the autoexec macro and just
run the make-table query
2. Use a VBA function for the problem.

I have written the following CBA function, but it doesn't populate the
table. I'm very new to functions so I'm at a loss how to get it to work.

Public Function IndARSC()

Dim CurrConn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim CurrDb As Database
Dim strConn As String
Dim strSQL As String

Set CurrDb = CurrentDb
Set CurrConn = New ADODB.Connection
strConn = "DSN=myDSN;User ID=myid;Pwd=mypsswd;"

CurrConn.Open strConn

strSQL = "SELECT * FROM PUB.arsc"
CurrConn.Execute strSQL
rs.Open "SELECT * FROM PUB.arsc", CurrConn, , , adCmdText

Do While Not rs.EOF

DoCmd.RunSQL "SELECT [PUB.arsc].[cono], [PUB.arsc].[custno],
[PUB.arsc].[name] INTO IndexARSC FROM [PUB.arsc]"
(PUB_arsc.cono=1))"
rs.MoveNext
Loop

End Function

Any suggestions would be much appreciated. Thanks.