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

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

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

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,

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,

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