Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel user enter a store # to query Access data base | Excel Discussion (Misc queries) | |||
store inventory sheet(ex:sports equipment store) | Excel Worksheet Functions | |||
Store Excel Results in Access | Excel Discussion (Misc queries) | |||
Store sheet names in Access | Excel Programming | |||
Access data base is able to store any number of records | Excel Worksheet Functions |