Store Data in Access
 

Hello,

I have the following code that pulls data directly from a sql server into a
spreadsheet. Now, I'd like to modify the code so I can store the data into an
Access table/database (still from Excel). I want to create a new Access
database everytime I run the code. I am not too familiar with Access objects
in Excel, so any helps will be greatly appreciated. Thanks very much in
advance.

Public Sub updateLoanCharacteristics()
Dim strQuery As String

Set conSQL = New ADODB.Connection
Set rsQuery = New ADODB.Recordset

conSQL.Open "Provider=MSDASQL;DSN=PM_SQL_SERVER_ARCHIVE;"

strQuery="Select * from tblData"

With rsQuery
.ActiveConnection = conSQL
.Open strQuery
ActiveWorkbook.ActiveSheet.Range("AU3").CopyFromRe cordset rsQuery
.Close
End With

conSQL.Close

Set conSQL = Nothing
Set rsQuery = Nothing

End Sub


Regards,
Adrian T

Store Data in Access
 

You can do the same thing with Access. Just point your connection string to
the Access database. Also add;

rsQuery.Close
just before
conSQL.Close

http://www.connectionstrings.com/ for DSN'less connection strings.


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Adrian T" wrote:
Hello,

I have the following code that pulls data directly from a sql server into
a
spreadsheet. Now, I'd like to modify the code so I can store the data into
an
Access table/database (still from Excel). I want to create a new Access
database everytime I run the code. I am not too familiar with Access
objects
in Excel, so any helps will be greatly appreciated. Thanks very much in
advance.

Public Sub updateLoanCharacteristics()
Dim strQuery As String

Set conSQL = New ADODB.Connection
Set rsQuery = New ADODB.Recordset

conSQL.Open "Provider=MSDASQL;DSN=PM_SQL_SERVER_ARCHIVE;"

strQuery="Select * from tblData"

With rsQuery
.ActiveConnection = conSQL
.Open strQuery
ActiveWorkbook.ActiveSheet.Range("AU3").CopyFromRe cordset rsQuery
.Close
End With

conSQL.Close

Set conSQL = Nothing
Set rsQuery = Nothing

End Sub


Regards,
Adrian T

Store Data in Access
 

Hi Dave,

How about if I want to create a new Access db? What's the code for that?

Sorry if it's a stupid question, I am just not familiar with the object. Any
references that I look into would be great.

Thanks,

"Dave Patrick" wrote:

You can do the same thing with Access. Just point your connection string to
the Access database. Also add;

rsQuery.Close
just before
conSQL.Close

http://www.connectionstrings.com/ for DSN'less connection strings.


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Adrian T" wrote:
Hello,

I have the following code that pulls data directly from a sql server into
a
spreadsheet. Now, I'd like to modify the code so I can store the data into
an
Access table/database (still from Excel). I want to create a new Access
database everytime I run the code. I am not too familiar with Access
objects
in Excel, so any helps will be greatly appreciated. Thanks very much in
advance.

Public Sub updateLoanCharacteristics()
Dim strQuery As String

Set conSQL = New ADODB.Connection
Set rsQuery = New ADODB.Recordset

conSQL.Open "Provider=MSDASQL;DSN=PM_SQL_SERVER_ARCHIVE;"

strQuery="Select * from tblData"

With rsQuery
.ActiveConnection = conSQL
.Open strQuery
ActiveWorkbook.ActiveSheet.Range("AU3").CopyFromRe cordset rsQuery
.Close
End With

conSQL.Close

Set conSQL = Nothing
Set rsQuery = Nothing

End Sub


Regards,
Adrian T

Store Data in Access
 

Try something like;

Const Jet4x = 5

CreateNewMDB "F:\DATA\mydatabase.mdb", Jet4x

Sub CreateNewMDB(FName, JetVer)
Dim Catalog
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=" & JetVer & _
";Data Source=" & FName
End Sub


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Adrian T" wrote:
Hi Dave,

How about if I want to create a new Access db? What's the code for that?

Sorry if it's a stupid question, I am just not familiar with the object.
Any
references that I look into would be great.

Thanks,

Store Data in Access
 

Hi Dave,

So, I am now able to create a new Access db from Excel. How do I store my
query's results directly from SQL to the new Access db without having to
create a new ODBC to create the connection?

From SQL to Excel, I use this code:

Set conSQL = New ADODB.Connection
Set rsQuery = New ADODB.Recordset

conSQL.Open "Provider=MSDASQL;DSN=PM_SQL_SERVER_ARCHIVE;"

With rsQuery
.ActiveConnection = conSQL
.Open strQuery
ActiveWorkbook.ActiveSheet.Range("AU3").CopyFromRe cordset rsQuery
.Close
End With

What command do I use to replace
ActiveWorkbook.ActiveSheet.Range("AU3").CopyFromRe cordset ?


Thanks,
Adrian T

"Dave Patrick" wrote:

Try something like;

Const Jet4x = 5

CreateNewMDB "F:\DATA\mydatabase.mdb", Jet4x

Sub CreateNewMDB(FName, JetVer)
Dim Catalog
Set Catalog = CreateObject("ADOX.Catalog")
Catalog.Create "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:Engine Type=" & JetVer & _
";Data Source=" & FName
End Sub


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Adrian T" wrote:
Hi Dave,

How about if I want to create a new Access db? What's the code for that?

Sorry if it's a stupid question, I am just not familiar with the object.
Any
references that I look into would be great.

Thanks,

Store Data in Access
 

I can't tell for sure what you're trying to do. Pieces are missing. Are you
pulling from SQL Server then writing into Access? What are strQuery and
rsQuery?

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Adrian T" wrote:
Hi Dave,

So, I am now able to create a new Access db from Excel. How do I store my
query's results directly from SQL to the new Access db without having to
create a new ODBC to create the connection?

From SQL to Excel, I use this code:

Set conSQL = New ADODB.Connection
Set rsQuery = New ADODB.Recordset

conSQL.Open "Provider=MSDASQL;DSN=PM_SQL_SERVER_ARCHIVE;"

With rsQuery
.ActiveConnection = conSQL
.Open strQuery
ActiveWorkbook.ActiveSheet.Range("AU3").CopyFromRe cordset rsQuery
.Close
End With

What command do I use to replace
ActiveWorkbook.ActiveSheet.Range("AU3").CopyFromRe cordset ?


Thanks,
Adrian T

Store Data in Access
 

Yes, I am trying to pull from SQL server then write into access directly. The
idea is to pull data subset (A) through the network (a sql server), store it
as an mdb locally, run subset queries (B) against the mdb, and store the
results in Excel. The goal is to make queries go faster. Instead of running
Bs through the network, I am pulling A once and run Bs.

A:
select id, propertytype, loantype
into [MS ACCESS].table1
from tbldata
where year=2006

B:
select property=
case
when propertytype='CO' then 'Condo'
when propertytype='SFD' then 'Single Family'
else 'Other'
end,
from [MS ACCESS].table1




"Dave Patrick" wrote:

I can't tell for sure what you're trying to do. Pieces are missing. Are you
pulling from SQL Server then writing into Access? What are strQuery and
rsQuery?

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

"Adrian T" wrote:
Hi Dave,

So, I am now able to create a new Access db from Excel. How do I store my
query's results directly from SQL to the new Access db without having to
create a new ODBC to create the connection?

From SQL to Excel, I use this code:

Set conSQL = New ADODB.Connection
Set rsQuery = New ADODB.Recordset

conSQL.Open "Provider=MSDASQL;DSN=PM_SQL_SERVER_ARCHIVE;"

With rsQuery
.ActiveConnection = conSQL
.Open strQuery
ActiveWorkbook.ActiveSheet.Range("AU3").CopyFromRe cordset rsQuery
.Close
End With

What command do I use to replace
ActiveWorkbook.ActiveSheet.Range("AU3").CopyFromRe cordset ?


Thanks,
Adrian T